Re: Database Design Question - Mailing list pgsql-general

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