Skip site navigation (1) Skip section navigation (2)

Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)

From: Gonzo Rock <GonzoRock(at)Excite(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
Date: 2001-07-27 18:02:15
Message-ID: 3.0.5.32.20010727110215.00c9bac0@postoffice.pacbell.net (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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!



Responses

pgsql-sql by date

Next:From: Gonzo RockDate: 2001-07-27 18:03:19
Subject: Database Design Question
Previous:From: Gonzo RockDate: 2001-07-27 18:01:57
Subject: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)

pgsql-general by date

Next:From: Gonzo RockDate: 2001-07-27 18:03:25
Subject: Database Design Question
Previous:From: Carlos Felipe ZirbesDate: 2001-07-27 17:28:47
Subject: RE: Visual Basic and PostgreSQL ODBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group