public inbox for [email protected]  

help / color / mirror / Atom feed

From: Gonzo Rock <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: RE: [SQL] Database Design Question
Date: Fri, 27 Jul 2001 13:21:44 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

OK... Fair Enough... Good Points indeed y'all.

Well... What about the problem of users trying to Query the Database??

You know... like when using Crystal Reports or something?.

SELECT * from HistoryTable
WHERE PartID = SomeInteger

vs

SELECT * from HistoryTable
WHERE PartNum = 12345636 AND PartRev = C

How are they supposed to know What the PartID is ??

Anyway, that I why I was considering changing... current users always have trouble peering into the database... They don't quite get it.




At 02:31 PM 7/27/01 -0400, Mike Mascari wrote:
>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
>[email protected]
>
>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!
>

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: RE: [SQL] Database Design Question
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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