RE: Database Design Question - Mailing list pgsql-sql

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:GonzoRock@Excite.com]
Sent: Friday, July 27, 2001 2:03 PM
To: pgsql-sql@postgresql.org
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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly