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!