Re: Database Design Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 05/13/04 

Date: Thu, 13 May 2004 14:43:26 -0400

What you call a "single primary key" is known as a "surrogate key". I think
this article from ASPFAQ covers both sides of the debate fairly well:

http://www.aspfaq.com/show.asp?id=2504

<sorengi@-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@TK2MSFTNGP11.phx.gbl...
> 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
>
> Can anyone see any advantages of using the Example 2 over using Example 1
method ?
>
>
> ---
> Posted using Wimdows.net NntpNews Component -
>
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Relevant Pages

  • Re: Storing Users/Groups
    ... websites can interface with this setup. ... > returns @outtable table (root_node int, group_key int, primary key ... > --40 members are members of 50 ... PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: Storing Users/Groups
    ... returns @outtable table (root_node int, group_key int, primary key ... --40 members are members of 50 ... PRIMARY KEY, FOREIGN KEY REFERENCES, ... Is it still possible to query all users of the websites with this additional functionality? ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)
  • Get all FKs with their table names, PK/FK fields
    ... The following SQL is correct: ... create table first (a int NOT NULL PRIMARY KEY, ... REFERENCES second) ...
    (microsoft.public.sqlserver.server)
  • Re: Adding new object to Typed Data Set and return the primary key
    ... CAST(TS AS INT) AS TS FROM AgencyNET.Office " + ... public TypedDataObjectProject newProject() { ... returning the new Primary Key value. ... on the singleton which then creates a new table adapter. ...
    (microsoft.public.dotnet.framework.adonet)