See below...

> -----Original Message-----
> From: John Taylor-Johnston [mailto:John.Taylor-
> Johnston@cegepsherbrooke.qc.ca]
> 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@address.com' ) ;
> INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2,
> 'second
> Name', 'email@anotheraddress.com' ) ;
>
> 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@address.com' ) ; -- updated the key value to allow for being
created automatically
INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null,
'second Name', 'email@anotheraddress.com' ) ; -- 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