Received: from mailout02.sul.t-online.de (mailout02.sul.t-online.com [194.25.134.17]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6RJ7Jf02613 for ; Fri, 27 Jul 2001 15:07:19 -0400 (EDT) (envelope-from A_Schnabel@t-online.de) Received: from fwd03.sul.t-online.de by mailout02.sul.t-online.de with smtp id 15QCx0-00086C-09; Fri, 27 Jul 2001 21:07:06 +0200 Received: from taska (520019441306-0001@[217.80.136.12]) by fwd03.sul.t-online.com with smtp id 15QCww-2EwvDcC; Fri, 27 Jul 2001 21:07:02 +0200 Message-ID: <001201c116cf$4b65f920$0201a8c0@aschnabel.homeip.net> From: A_Schnabel@t-online.de (Andre Schnabel) To: "Gonzo Rock" , References: <3.0.5.32.20010727110325.00c9e430@postoffice.pacbell.net> Subject: Re: Database Design Question Date: Fri, 27 Jul 2001 21:06:50 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C116E0.0DEB62E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Sender: 520019441306-0001@t-dialin.net X-Archive-Number: 200107/978 X-Sequence-Number: 13098 This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C116E0.0DEB62E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 mu= ch 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 Partty= pe 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 -----=20 From: Gonzo Rock=20 To: pgsql-general@postgresql.org=20 Sent: Friday, July 27, 2001 8:03 PM Subject: [GENERAL] Database Design Question A Question for those of you who consider yourself crack Database Designer= s. I am currently moving a large database(100+Tables) into pgSQL... with the= intention of deploying against 'any' SQL database in the future. The devel= opment 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 Integ= er ID like this: *=3DAPrimaryKey PartTypes Customer Parts --------- -------- ----- PartTypeID CustomerID PartID *PartType *Customer PartTypeID Address CustomerID *PartNumber(2FieldPrimaryKey) *PartRevision(2FieldPrimaryKey) PartName =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 HOWEVER; I have read lots of texts describing the Relational Design shoul= d be instead like this: *=3DAPrimaryKey PartTypes Customer Parts --------- -------- ----- *PartType *Customer PartType=20=20 Address *PartNumber(2FieldPrimaryKey) *PartRevison(2FieldPrimaryKey) PartName=20=20=20=20 Customer =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 Both Techniques have a unique foreign key back to the parent tables but o= ne 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 befor= e 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 ------=_NextPart_000_000F_01C116E0.0DEB62E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Don't really know, if I am a crack .. but ...
 
Your 1st Design would be faster when joining the tables= in a=20 query or view. Furthermore an index on the id's (should be integers, right?= )=20 would use much less storage space than an index on=20 character-fields.
 
The 2nd design is preferred by theoretical purists. The= data=20 are much more selfexplaining. If you only have a Parts-record you can see t= o=20 which Parttype an Costumer it belongs without qeurying the other tables. Wi= th=20 your 1st design you had to.
 
I think it's a question of performance, storagespace an= d=20 readability.
If you need high performace use the 1st Design.<= /DIV>
If you need a design, readable by people who don't work= day by=20 day with it, use the 2nd method.
 
It's only my opinion, must not be right.
 
CU,
Andre
----- Original Message -----
Fro= m:=20 Gonz= o=20 Rock
To: pgsql-general@postgresql.org= =20
Sent: Friday, July 27, 2001 8:03 P= M
Subject: [GENERAL] Database Design= =20 Question

A Question for those of y= ou who=20 consider yourself crack Database Designers.

I am currently moving = a=20 large database(100+Tables) into pgSQL... with the intention of deploying= =20 against 'any' SQL database in the future. The development side will be=20 rigorously using Standard SQL constructs with no unique/proprietary=20 extensions.

My question concerns establishing the=20 relationships.

Currently Relationships between tables are establis= hed=20 via a Unique Integer ID like=20 this:

*=3DAPrimaryKey

 PartTypes   =20 Customer     Parts
 ---------  &nbs= p;=20 --------     -----
 PartTypeID  =20 CustomerID   PartID
*PartType   =20 *Customer    =20 PartTypeID
          = ;   =20 Address     =20 CustomerID
          = ;            &n= bsp;  =20 *PartNumber(2FieldPrimaryKey)
      &nbs= p;            &= nbsp;     =20 *PartRevision(2FieldPrimaryKey)
      &n= bsp;            = ;       =20 PartName
          &= nbsp;          =20

HOWEVER; I have read lots of texts describing the Relational Desi= gn=20 should be instead like=20 this:

*=3DAPrimaryKey

 PartTypes   =20 Customer     Parts
 ---------  &nbs= p;=20 --------     -----
*PartType   =20 *Customer     PartType =20
           &nb= sp; =20 Address    =20 *PartNumber(2FieldPrimaryKey)
      &nbs= p;            &= nbsp;     =20 *PartRevison(2FieldPrimaryKey)
      &nb= sp;            =        =20 PartName   =20
           &nb= sp;            =   =20 Customer
          &= nbsp;           &nbs= p;            &= nbsp;     =20
Both Techniques have a unique foreign key back to the parent tables b= ut=20 one uses No.Meaningful.Info.Integer.Data for the ForeignKey while the sec= ond=20 uses Human.Understandable.ForeignKeys

Is one recommended over the= =20 other??? Sure appreciate the commentary before I get in too deep with all= =20 these tables.

Thanks!



---------------------------(e= nd of=20 broadcast)---------------------------
TIP 5: Have you checked our exte= nsive=20 FAQ?

http://www.= postgresql.org/users-lounge/docs/faq.html ------=_NextPart_000_000F_01C116E0.0DEB62E0--