Re: Database Design Question

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(at)t-online(dot)de (Andre Schnabel)
>To: "Gonzo Rock" <GonzoRock(at)Excite(dot)com>, <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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