Database Design Question

From: Gonzo Rock <GonzoRock(at)Excite(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Database Design Question
Date: 2001-07-27 18:03:19
Message-ID: 3.0.5.32.20010727110319.00c9bac0@postoffice.pacbell.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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!

Browse pgsql-sql by date

  From Date Subject
Next Message Mike Mascari 2001-07-27 18:31:58 Re: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
Previous Message Gonzo Rock 2001-07-27 18:02:15 Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)