Обсуждение: More Database Design Stuff

This may seem like a newbie type of question:  for what reason would you
need a multi-column primary key.  None of the books that I have read explain
why or why not.  It seems that the more normalized your database is, the
less need for multi-column primary keys.  Are multi-column primary keys
considered bad form?  I have never needed to use them, but I happen to use
auto-incrementing integer primary keys on all tables, as discussed earlier
this week.  Any thoughts on this subject is appreciated.


Jimmie Fulton
Systems Administrator
Environmental Health & Safety Office
Emory University School Of Medicine

Well, consider this:

You have a customer list, each customer with his own unique customer number.
Each of your customers has at least one address but some of them have 2 or
more. Therefore, you have to create two tables, say one is called 'cust' the
other 'cust_addr'.
The 'cust_addr' table now needs to have a unique primary key, for which the
cust_no alone doesn't qualify, so you need to have a second column, say you
decide on 'addr_type' and allow for 'I' - Invoice or 'D' - Delivery (very
simplified example). You can now create a unique primary key on this table
on two columns, 'cust_no' and 'addr_type'.

I don't really care if people say it's bad to do this kind of thing, I like
to tell them that I will do what works for me, and there is usually not much
to argue about that point ;).

Best regards,
Chris


----- Original Message -----
From: "Jimmie Fulton" <JFulton@ehso.emory.edu>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, August 02, 2001 3:23 PM
Subject: [SQL] More Database Design Stuff


> This may seem like a newbie type of question:  for what reason would you
> need a multi-column primary key.  None of the books that I have read
explain
> why or why not.  It seems that the more normalized your database is, the
> less need for multi-column primary keys.  Are multi-column primary keys
> considered bad form?  I have never needed to use them, but I happen to use
> auto-incrementing integer primary keys on all tables, as discussed earlier
> this week.  Any thoughts on this subject is appreciated.
>
>
> Jimmie Fulton
> Systems Administrator
> Environmental Health & Safety Office
> Emory University School Of Medicine
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Chris,

> I don't really care if people say it's bad to do this kind of thing,
> I like
> to tell them that I will do what works for me, and there is usually
> not much
> to argue about that point ;).

In most cases, simple surrogate keys (i.e. cust_id SERIAL) are the
easiest things to deal with.  However, there are many cases where a
multi-column key can be superior.

Fabian Pascal has an excellent disussion of evaluating the suitability
of candidate keys in selecting a primary key in Practical Issues in
Database Design.  It may also be on his web site
(http://www.firstsql.com/dbdebunk/).

(Candidate keys are unique combos of information in a table that could
be a primary key if you wanted)

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco