From: MD

To: Editor

Date: 13 May 2004

 

 

Do you guys have a paper which covers the below question?

 

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.

 

 

Relationships:

 

Language to Brochure   = one-to-many

Brochure to Heading  = many-to-many

Heading to Paragraph  = one-to-many

 

-- *** Example 1 COMPOSITE FOREIGN KEY Code ***

 

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)***

 

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

 

 

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

 

Are there any advantages of using the Example 2 over using Example 1 method ?

 

If not, do you know where I can get answers ?

 

 

From: Fabian Pascal

To: MD

 

We cannot address specific design issues, because we don't feel we can get sufficient and clear knowledge of the reality to be modeled via email.

 

We can refer you to my chapter on keys in PRACTICAL ISSUES IN DATABASE MANAGEMENT, and Chris Date's chapter in his AN INTRODUCTION TO DATABASE SYSTEMS, both available via the books page.

 

A PRACTICAL DATABASE FOUNDATIONS paper on keys is forthcoming.

 

 

Ed. Comment: In our writings, including the referenced books, we warn practitioners not to contaminate logical design—which includes key selection—with physical implementation considerations such as indexing and join performance. Only after the appropriate logical design has been completed should physical details be considered.

 

 

Posted 08/13/04