Re: Database Design Question

From: David Browne (meat_at_hotmail.com)
Date: 05/13/04 

Date: Thu, 13 May 2004 13:43:32 -0500

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

This is one of my favorite designs. It does a couple of things for you
quite nicely.

First off it gives you a single, highly efficient access path to the child
rows, and simultaneously supports your foregn key with a clustered index.
This is especially effective for modeling parent/child relationships where
the child rows will usually be accessed through the parent row. And
especially ineffective elsewhere.

Remember you will need a supporting index on the foregn key column in any
case, and if you make the foreign key the leading columns in the primary
key, you may need a secondary index on the identity column.
. . .

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

If the foreign key does not lead the Primary Key, you need a secondary index
to support the foreign key.

  create index ix_brocure_lang on tbBrocure(LanguageId)

This is incredibly important for queries like
  select * from tbBrocure where LanguageId = 123
or
  delete tbLanguage where LanguageId = 123

Having LanguageID as the second column in the primary key just doesn't help.
And so Example 1, as written, is pretty useless. You could have left
LanguageID out of the primary key altogher.

Example 1 should be

 CREATE TABLE tbBrochure
 (
 BrochureId int identity(1,1) not null,
 LanguageId int not null,
 Title varchar(255) not null
 PRIMARY KEY CLUSTERED(LanguageId,BrochureId),
 FOREIGN KEY (LanguageId)
 REFERENCES tbLanguage(LanguageId)
 )

Then the foregn key is supported by an index, but you may need a secondary
index on BrocureId to support queries like

  select * form tbBrochure where BrochureId = 1234

David

David

Relevant Pages

  • Re: FAQ? factors influencing choice of data type for primary key
    ... >>example, an Autonumber as a primary key, are there ... >>it is a foreign key? ... >don't need to bring the lookup table or any indexes into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: OT: SQL & Dave
    ... A&E broadcast ... My initial thought was to have the "show number" be the primary key, ... so that it'll be the foreign key in other tables (guests, staff, ... could I set up those other tables (guest, ...
    (alt.fan.letterman)
  • Re: Data changes but reverts.
    ... > trust my data to a wizard-built form. ... > 3) What is the primary and foreign key in your relationship ... I did a TABLEUPDATE() and it's now working! ... The symbol field is the primary key in the Symbols table and it is ...
    (microsoft.public.fox.programmer.exchange)
  • RE: Creating an DB for an Office Rota/Skills
    ... Also how do i set up a foreign key? ... primary key because atm i have to type them in eg. 1.2.3.4.5 for each persons ... "Duane Hookom" wrote: ... fields for skills and/or days then you are on your own. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)