Database Design Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Design Question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-20 : 09:11:01

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

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,

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-05-20 : 17:03:47

Well, it looks like you've run out of space in posting your question. So given what you have here, let me say that in example 1 (composite PRIMARY key on tbBrochureHeadingMap and tbParagragh) that you are properly using the PK to eliminate duplicates. In example 2 where you have added IDENTITY fields as primary keys for those two tables, there is nothing preventing duplicate entries because the PK is a meaningless number and any INSERT statement will just increment the number and stuff in the duplicates. In example 1, any INSERT with the same combination of PK fields will fail after the first one.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources