pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMore Database Design Stuff
3+ messages / 3 participants
[nested] [flat]
* More Database Design Stuff
@ 2001-08-02 20:23 Jimmie Fulton <JFulton@ehso.emory.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Jimmie Fulton @ 2001-08-02 20:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: More Database Design Stuff
@ 2001-08-02 20:59 Chris Ruprecht <chrup999@yahoo.com>
parent: Jimmie Fulton <JFulton@ehso.emory.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Chris Ruprecht @ 2001-08-02 20:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: More Database Design Stuff
@ 2001-08-02 22:36 Josh Berkus <josh@agliodbs.com>
parent: Chris Ruprecht <chrup999@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2001-08-02 22:36 UTC (permalink / raw)
To: Chris Ruprecht <chrup999@yahoo.com>; pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-08-02 22:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-02 20:23 More Database Design Stuff Jimmie Fulton <JFulton@ehso.emory.edu>
2001-08-02 20:59 ` Chris Ruprecht <chrup999@yahoo.com>
2001-08-02 22:36 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox