See below... > -----Original Message----- > From: John Taylor-Johnston [mailto:John.Taylor- > [EMAIL PROTECTED] > Sent: Thursday, March 20, 2008 2:17 PM > To: Sebastian Mendel; mysql@lists.mysql.com > Subject: Re: relational tables > > DROP TABLE IF EXISTS `person`; > CREATE TABLE `person` ( > `person_id` int(11) NOT NULL auto_increment, > `name` varchar(255) default NULL, > `email` varchar(255) default NULL, > PRIMARY KEY (`person_id`), > KEY `email` (`email`), > KEY `name` (`name`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; > > INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1, > 'Name', > '[EMAIL PROTECTED]' ) ; > INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, > 'second > Name', '[EMAIL PROTECTED]' ) ; > > DROP TABLE IF EXISTS `shopping`; > CREATE TABLE IF NOT EXISTS `shopping` ( > `shopping_id` int(11) NOT NULL, > `email` varchar(255) default NULL, > `name` varchar(255) default NULL, > PRIMARY KEY (`shopping_id`), > UNIQUE KEY `email` (`email`), > UNIQUE KEY `name` (`name`), > FOREIGN KEY (`email`) REFERENCES `person` (`email`), > FOREIGN KEY (`name`) REFERENCES `person` (`name`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; >
Something doesn't smell right with the 'shopping' table. Rather than using what appears to be the same values (aside from the person_id as opposed to the shopping_id), wouldn't it be more effective to have the shopping table have 'shopping_id' and 'person_id' fields? With that approach, you could get away from having the same data in two tables. But, you'll have to start building ways to look up the person_id based on name and email values in order to populate the shopping table. Took a flyer at an updated version of the above (not knowing what your intent is): DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `person_id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `email` varchar(255) default NULL, PRIMARY KEY (`person_id`), KEY `email` (`email`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'Name', '[EMAIL PROTECTED]' ) ; -- updated the key value to allow for being created automatically INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'second Name', '[EMAIL PROTECTED]' ) ; -- same thing here DROP TABLE IF EXISTS `shopping`; CREATE TABLE IF NOT EXISTS `shopping` ( `shopping_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, PRIMARY KEY (`shopping_id`), FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; The syntax of the above might not be 100%...but it looks to be close enough. No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.21.7/1335 - Release Date: 3/19/2008 9:54 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
