From: "omid omoomi" <oomoomi@hotmail.com>
To: A_Schnabel@t-online.de, GonzoRock@Excite.com, pgsql-general@postgresql.org
Subject: Re: Database Design Question
Date: Fri, 27 Jul 2001 20:40:48 
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <F52wYS3Vbfb8tnurzwc00007e01@hotmail.com>

Hi,
In addition I think, using that integer primary keys would be useful for the 
databases which does not support CASCADE ON UPDATE.
Say you would have to change a PartType for any reason,using the integer 
format, you will face no problem on tables integrity, updating the 
description at the PartTypes table.
But currently PG supports the CASCADE UPDATEs.
Omid

>From: A_Schnabel@t-online.de (Andre Schnabel)
>To: "Gonzo Rock" <GonzoRock@Excite.com>, <pgsql-general@postgresql.org>
>Subject: Re: [GENERAL] Database Design Question
>Date: Fri, 27 Jul 2001 21:06:50 +0200
>
>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 -----
>   From: Gonzo Rock
>   To: pgsql-general@postgresql.org
>   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


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp