Easy Modeling Qs
From: Jonathan Driller <jdriller_at_orchid.org>
Date: 1 Jul 2002 13:47:43 -0700
Message-ID: <a8cff9fb.0207011247.3b9cdfc0_at_posting.google.com>
I have just ordered a book on modeling but am struggling with some basic Qs that yield conflicting answers on the Web: 1)In 2 tables with a 1:1 relationship, I think I could combine them into 1 table but think it would be more efficient to have 2. Can I use the primary key of the 1 table as the foriegn AND primary key of the second? If yes, do I simply say something like "create table second_table(x_id not null primary key references main_table, anum int(2));"? Normally I would use an identity column for each table but now I am thinking that is a waste of space when I can uniquely identify a column with another tables primary key.
Date: 1 Jul 2002 13:47:43 -0700
Message-ID: <a8cff9fb.0207011247.3b9cdfc0_at_posting.google.com>
I have just ordered a book on modeling but am struggling with some basic Qs that yield conflicting answers on the Web: 1)In 2 tables with a 1:1 relationship, I think I could combine them into 1 table but think it would be more efficient to have 2. Can I use the primary key of the 1 table as the foriegn AND primary key of the second? If yes, do I simply say something like "create table second_table(x_id not null primary key references main_table, anum int(2));"? Normally I would use an identity column for each table but now I am thinking that is a waste of space when I can uniquely identify a column with another tables primary key.
2) This one is maybe a consequence of staring at things too long: I have a table that is a 1:n to 3 different tables...can I do that? I am used to association tables for many to many relationships but these are all 1:n. Seems like maybe this is bad design but I can't figure out a better way. I have never done as complicated a schema as this and have only ever had single 1:n relationships between all related tables.
All help greatly appreciated.
JD Received on Mon Jul 01 2002 - 22:47:43 CEST