Postgres Pro
Downloads
Home   >   mailing lists

Re: Database Design Question - Mailing list pgsql-general

From A_Schnabel@t-online.de (Andre Schnabel)
Subject Re: Database Design Question
Date
Msg-id 001201c116cf$4b65f920$0201a8c0@aschnabel.homeip.net
Whole thread Raw
In response to Database Design Question  (Gonzo Rock <GonzoRock@Excite.com>)
Responses PostgreSQL to Dia program
List pgsql-general
Tree view
Don't really know, if I am a crack .. but ...
 
Your 1st Design would be faster when joining the tables in a query or view. Furthermore an index on the id's (should be integers, right?) would use much less storage space than an index on character-fields.
 
The 2nd design is preferred by theoretical purists. The data are much more selfexplaining. If you only have a Parts-record you can see to which Parttype an Costumer it belongs without qeurying the other tables. With your 1st design you had to.
 
I think it's a question of performance, storagespace and readability.
If you need high performace use the 1st Design.
If you need a design, readable by people who don't work day by day with it, use the 2nd method.
 
It's only my opinion, must not be right.
 
CU,
Andre
----- Original Message -----
Sent: Friday, July 27, 2001 8:03 PM
Subject: [GENERAL] Database Design Question

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!



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

pgsql-general by date:

Previous
From: Mike Mascari
Date:
Subject: Re: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
Next
From: Gonzo Rock
Date:
Subject: RE: [SQL] Database Design Question