Bruce, It all depends on what you are storing in foo1 and foo2.....
For a one-to-many relationship, you need to have that ID column in your child table to reference the parent record. The "classic" example of a one-to-many relationship is Companies to Employees. In the real world there are some cases where one person "could" work for more than one company at a time but not for this example.... CREATE TABLE Company ( ID int auto_increment , Name varchar(40) primary key ,Key(ID) ); CREATE TABLE Employee ( ID int auto_increment , Company_ID int , Name varchar(40) primary key ,KEY(ID) ); INSERT Company (Name) values ('ABC Corp'),('XYZ Supply Company'),('Some Other Company'); INSERT Employee (Name, Company_ID) Value ('J Smith',1), ('K Jones',1), ('S Serpentine', 2), ('B Dog', 2), ('S Pony',3); This results in the following data: SELECT* FROM Company; +----+--------------------+ | ID | Name | +----+--------------------+ | 1 | ABC Corp | | 2 | XYZ Supply Company | | 3 | Some Other Company | +----+--------------------+ 3 rows in set (0.04 sec) SELECT * FROM Employee; +----+------------+--------------+ | ID | Company_ID | Name | +----+------------+--------------+ | 1 | 1 | J Smith | | 2 | 1 | K Jones | | 3 | 2 | S Serpentine | | 4 | 2 | B Dog | | 5 | 3 | S Pony | +----+------------+--------------+ 5 rows in set (0.00 sec) This shows that J Smith and K Jones both work for ABC Corp. The value in the Company_Id field must exist in both tables to form that relationship. What exactly are you trying to model? In regards to your question about unique ID numbers between tables, you may be looking for the term "foreign key" but I won't know if that applies to you until I understand your situation better. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "bruce" <[EMAIL PROTECTED] To: <[EMAIL PROTECTED]> ink.net> cc: Fax to: 07/08/2004 12:49 Subject: table layout structure PM Please respond to bedouglas hi... i have an issue/prob and i'm trying to figure out the best approach... i have multiple tables foo1, foo2 foo1: foo2: - name - name - id - foo1_id - id - unique key (id, name, foo1_id) my question: does mysql provide a way to do a unique key on table items between multiple tables, or do i need to essentially create my logic/code to handle/ensure the relationships myself. im my above exmaple, i'd rather not have to have "foo1_id" in table foo2 as it is a duplicate of "foo1.id".... i'd really like to be able to do something like "unique key (id, name, foo1.id)"<<<<< any thoughts/comments/criticisms... thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]