Thread: Database Design Question

Database Design Question

From

Gonzo Rock

Date:

27 July 2001, 17:25:52

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
databasein 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
theForeignKey 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!

Re: Database Design Question

From

A_Schnabel@t-online.de (Andre Schnabel)

Date:

27 July 2001, 18:07:28

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

PostgreSQL to Dia program

From

"Brent R. Matzelle"

Date:

27 July 2001, 19:21:35

Someone just posted a PostgreSQL to Dia automatic diagram
creation tool.  I have not tested it, but if it works it could
be a huge help.

http://www.zort.ca/postgresql/

Justin, this could definately be a techdocs link.

Brent

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Re: Database Design Question

From

"omid omoomi"

Date:

27 July 2001, 19:40:51

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

Re: PostgreSQL to Dia program

From

Justin Clift

Date:

28 July 2001, 02:14:54

Hi Brent,

Thanks for this!  I'll put it on my ToDo list for adding.

Good thing I came across your email, about 2/3 to 3/4 of all the
pgsql-general emails I never even get time to read.  :(

If you REALLY want to make sure I read something, please CC it to me of
course!

:-)

Regards and best wishes,

Justin Clift


"Brent R. Matzelle" wrote:
>
> Someone just posted a PostgreSQL to Dia automatic diagram
> creation tool.  I have not tested it, but if it works it could
> be a huge help.
>
> http://www.zort.ca/postgresql/
>
> Justin, this could definately be a techdocs link.
>
> Brent
>
> __________________________________________________
> Do You Yahoo!?
> Make international calls for as low as $.04/minute with Yahoo! Messenger
> http://phonecard.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi