Database Design Question - Mailing list pgsql-sql

From Gonzo Rock
Subject Database Design Question
Date
Msg-id 3.0.5.32.20010727110319.00c9bac0@postoffice.pacbell.net
Whole thread Raw
List 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
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!




pgsql-sql by date:

Previous
From: Gonzo Rock
Date:
Subject: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
Next
From: Jimmie Fulton
Date:
Subject: RE: Database Design Question