在MySQL中构建用户数据[关闭]

I'm setting up a new web service, and need some help with organizing the data in my application. My application's written in PHP, and I plan to store all user data in using MySQL Databases.

I will have a bunch of users, and each of those users will have a bunch of data that is tied to their account. It's a note taking application, and so, naturally, for every user, I need to keep track of the number of notes, and the content of each of those notes.

How do I go about structuring this and translating this into MySQL tables? I know I have to do something that revolves around linked (related) tables, but can't seem to pinpoint it.

I have 10x1GB databases.

Have the following tables (you can choose your own names);

tbl_users
int(4)       userid (PRIMARY)
varchar(128) firstname
varchar(128) lastname

tbl_notes
int(8)       noteid (PRIMARY)
int(4)       userid
text         notecontent

As you can see, userid is the relation between the two tables (1 to many in this case).

See Database normalization from Wikipedia

Try following queries to create your tables:

Users:

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email_address` varchar(96) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `birth_date` datetime DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `street` varchar(16) NOT NULL,
  `address` varchar(64) NOT NULL,
  `city` varchar(32) NOT NULL,
  `state` varchar(32) DEFAULT NULL,
  `country` int(11) NOT NULL DEFAULT '0',
  `postcode` varchar(10) NOT NULL,
  `status` int(1) DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Notes:

CREATE TABLE IF NOT EXISTS `notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `date_added` datetime NOT NULL,
  `date_sent` datetime DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  `locked` int(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;