Re: Easy Modeling Qs

Hi,

>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.

Yes, this technique is called "vertical partitioning" ((opposite to "horizontal partitioning" based on age/use criteria when 2 or more tables have absolutely the same columns but store different rows (e.g "Sales_2000" and "Sales_2001" tables that store rows for sales made by company in 2000 and 2001 accordingly))

Yes, "vertical partitioning" can be -efficient- but sometimes (say if you have some columns being updated/queried more frequently than others, then it's better to put them in the separate table)

>Can I use
>the primary key of the 1 table as the foriegn AND primary key of the
>second?

Yes, something like this (MSSQL syntax, if you meant "IDENTITY" property):

/* --------- BEGIN SQL --------- */ CREATE TABLE Table1
(
 Col1 INTEGER NOT NULL IDENTITY(1,1),
 /*other column definitions*/
 CONSTRAINT PK_Table1 PRIMARY KEY
)

CREATE TABLE Table2
(
 Col1 INTEGER NOT NULL IDENTITY(1,1),
 /*other column definitions*/
 CONSTRAINT PK_Table2 PRIMARY KEY
)

ALTER TABLE Table2
 ADD CONSTRAINT FK_Table2__Table1 FOREIGN KEY (Col1) REFERENCES Table1 (Col1)

/* --------- END SQL --------- */
>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.

That's ok, associative entities/tables always used this way --to eliminate many-to-many relationships by resolving/changing them into 2 or more one-to-many relationships.

Hope this helps

Alex P
Sys Analyst,mcdba,ocp

~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ 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

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG