I'm by no means a crack database designer, but I do have my ideas on this
subject. I prefer that every table has a unique integer (auto-incrementing)
primary key. Why? Consistency. With the alternative, some tables may not
have a clear-cut candidate for a unique id. In your example, you had
"customer". How many "John Smith"s would it take before we decide that is
not a good identifier. On the other hand, some tables would have perfectly
logical identifiers. Part numbers, SSNs.... So, you would need to create
some tables with integer primary keys, and others would have some other
natural identifier. That to me is inconsistent. Every table should be,
IMHO, predictable in it's definition of a primary key. I don't even have to
guess what the names of my primary keys are either because the are all named
<tablename>_ID. Always. I've only come up with these thoughts on my own,
and have not extensively tried the other way, so I'd be interested in
hearing other's ideas for the other side.
Thanks,
Jimmie Fulton
Systems Administrator
Environmental Health & Safety Office
Emory University School Of Medicine
-----Original Message-----
From: Gonzo Rock [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 27, 2001 2:03 PM
To: [EMAIL PROTECTED]
Subject: [SQL] Database Design Question
A Question for those of you who consider yourself crack Database Designers.
I am currently moving a large database(100+Tables) into pgSQL... with the
intention of deploying against 'any' SQL database in the future. The
development side will be rigorously using Standard SQL constructs with no
unique/proprietary extensions.
My question concerns establishing the relationships.
Currently Relationships between tables are established via a Unique Integer
ID like this:
*=APrimaryKey
PartTypes Customer Parts
--------- -------- -----
PartTypeID CustomerID PartID
*PartType *Customer PartTypeID
Address CustomerID
*PartNumber(2FieldPrimaryKey)
*PartRevision(2FieldPrimaryKey)
PartName
HOWEVER; I have read lots of texts describing the Relational Design should
be instead like this:
*=APrimaryKey
PartTypes Customer Parts
--------- -------- -----
*PartType *Customer PartType
Address *PartNumber(2FieldPrimaryKey)
*PartRevison(2FieldPrimaryKey)
PartName
Customer
Both Techniques have a unique foreign key back to the parent tables but one
uses No.Meaningful.Info.Integer.Data for the ForeignKey while the second
uses Human.Understandable.ForeignKeys
Is one recommended over the other??? Sure appreciate the commentary before I
get in too deep with all these tables.
Thanks!
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
