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
databasein 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
theForeignKey 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!