Database Design Question

[Michael D]

What are the pros and cons of the following two design methods ?

(1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.

POOR MAN'S ERD PROVIDED FOR SUMMARY OVERVIEW (Code provided below)

*** Example 1 COMPOSITE FOREIGN KEY ***
PK = Primary Key
FK = Foreign Key

Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many

tbLanguage tbBrochure
---------------------- ----------------------
- LanguageId (PK) - -----\ - BrochureId (PK) -
- LangName - \----> - LanguageId (PK)(FK)-
---------------------- / - Title -
/ / ---------------------
/ /
/ /
tbBrochureHeadingMap / / tbHeading
---------------------- <-----/ / ----------------------
- BrochureId (PK)(FK)- <------/ -- - HeadingId (PK) -
- LanguageId (PK)(FK)- / - HeadingText -
- HeadingId (PK)(FK)- <--------/ ----------------------
---------------------- |
/
tbParagraph /
---------------------- /
- BrochureId (PK)(FK)- <----------/
- LanguageId (PK)(FK)-
- HeadingId (PK)(FK)-
- SequenceNo (PK) -
- ParagraphText -
----------------------
(2) Using a new key to form a single primary key of a table, and
placing parent tables as only foreign keys -- as in Example 2.

*** Example 2 SINGLE PRIMARY KEY ***
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many
tbLanguage tbBrochure
---------------------- ----------------------
- LanguageId (PK) - -----\ - BrochureId (PK) -
- LangName - \----> - LanguageId (FK) -
---------------------- - Title -
----------------------
|
|
tbBrochureHeadingMap | tbHeading
---------------------------- / ----------------------
- BrochureHeadingMapId (PK)- / - HeadingId (PK) -
- BrochureId (FK) - <---/ / - HeadingText -
- HeadingId (FK) - <--------/ ----------------------
---------------------------- |
|
tbParagraph /
---------------------- /
- ParagraphId(PK) - /
- HeadingId (FK) - <--------------/
- SequenceNo -
- ParagraphText -
----------------------
It has been argued that Example 1: COMPOSITE FOREIGN KEY has the
following pros, over Example 2:

1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of
Nine (9) in Example 2.

2) Queries can be created with fewer joins.

For example: (one join in Example 1)

SELECT b.Title,
p.ParagraphText

FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)

Instead Of: (two joins in Example 2)

SELECT b.Title,
p.ParagraphText

FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId

Can anyone see any advantages of using the Example 2 over using
Example 1 method ?
-- *** Example 1 COMPOSITE FOREIGN KEY Code (SQL Server 2000) ***

if exists (select * from dbo.sysobjects where id =
object_id(N'[tbParagraph]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tbParagraph]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochureHeadingMap]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadingMap]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbBrochure]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbLanguage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbLanguage]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbHeading]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbHeading]
GO
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangName varchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
SequenceNo int not null,
ParagraphText varchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***

if exists (select * from dbo.sysobjects where id =
object_id(N'[tbParagraph]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tbParagraph]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochureHeadingMap]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadingMap]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbBrochure]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbLanguage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbLanguage]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbHeading]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbHeading]
GO

CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangName varchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphId int identity(1,1) not null,
HeadingId int not null,
SequenceNo int not null,
ParagraphText varchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
go

[Erland Sommarskog]

[posted and mailed, please reply in news]

Michael D (so*****@yahoo.com) writes:

What are the pros and cons of the following two design methods ?

(1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
...
(2) Using a new key to form a single primary key of a table, and
placing parent tables as only foreign keys -- as in Example 2.

#1 wins, hands down.

If you get very many columns in your key, it may be tempting to
introduce a artificial key.

I had one case in our data base where a table with a four-column key
needed a subtable, with two more columns in the key. I though a six-
column key was a bit too much, so I added an artificial key, and used
that in the subtable. Thus I had:

CREATE TABLE summary (id int NOT NULL,
fk_a int NOT NULL,
fk_b int NOT NULL,
fk_c int NOT NULL,
fk_d int NOT NULL,
value float NOT NULL,
CONSTRAINT pk_sum PRIMARY KEY(id),
CONSTRAINT u_sum UNIQUE(fk_a, fk_b, fk_c, fk_d))
go
CREATE TABLE details (id int NOT NULL,
fk_e int NOT NULL,
flag char(1) NOT NULL,
value float NOT NULL,
CONSTRAINT pk_details PRIMARY KEY (id, fk_e, flag),
CONSTRAINT fk_details FOREIGN KEY (id) REFERENCES parent(id))

Then much later on, I had reason to write queries against these tables,
including updates where selection was on fk_a. It was extremely messy.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp