pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
3+ messages / 2 participants
[nested] [flat]

* Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
@ 2001-07-27 18:01  Gonzo Rock <GonzoRock@Excite.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Gonzo Rock @ 2001-07-27 18:01 UTC (permalink / raw)
  To: pgsql-sql

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!





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
@ 2001-07-27 18:02  Gonzo Rock <GonzoRock@Excite.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Gonzo Rock @ 2001-07-27 18:02 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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!





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe)
@ 2001-07-27 18:31  Mike Mascari <mascarm@mascari.com>
  parent: Gonzo Rock <GonzoRock@Excite.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Mike Mascari @ 2001-07-27 18:31 UTC (permalink / raw)
  To: Gonzo Rock <GonzoRock@Excite.com>; +Cc: pgsql-general@postgresql.org

I prefer using unique integer ids generated from sequences rather than
keys composed of meaningful values.

Advantages:

Client side applications can store/handle the unique integer ids more
readily than having to deal with composite primary keys composed of
varying data types. For example, I can stuff the id associated with a
particular record easily in list boxes, combo boxes, edit controls, etc.
via SetItemData() or some other appropriate method. Its a bit more
complicated to track database records via composite keys of something
like: part no, vendor no, vendor group.

Updating the data doesn't require cascading updates. If you use keys
with meaning, the referential integrity constraints must support
cascading updates so if the key changes in the primary table the change
is cascaded to all referencing tables as well. Earlier versions of most
databases (Access, Oracle, etc.) only provided cascading deletes under
the assumption you would be using sequence generated keys.

Downside:

Many queries might require more joins against the primary table to fetch
the relevant information associated with the numerical id, whereas keys
composed of solely the values with which they are associated might not
require the joins, which will speed some applications. I now have some
queries with 20-way joins. But PostgreSQL provides a way to explicitly
set the path the planner will choose and so the execution of the query
is instantaneous. I'm not sure about other databases. In earlier
versions, I had to denormalize a bit solely for performance reasons.

In the past, I used to use composite keys and switched to the purely
sequence generated path and don't regret it at all. Of course, you'll
still have a unique constraint on the what-would-have-been meaningful
primary key.

Hope that helps,

Mike Mascari
mascarm@mascari.com

Gonzo Rock wrote:
> 
> 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!



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-07-27 18:31 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-27 18:01 Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe) Gonzo Rock <GonzoRock@Excite.com>
2001-07-27 18:02 Re: D308-E9AF-4C11 : CONFIRM from pgsql-sql (subscribe) Gonzo Rock <GonzoRock@Excite.com>
2001-07-27 18:31 ` Mike Mascari <mascarm@mascari.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox