Re: RE: [SQL] Database Design Question - Mailing list pgsql-general

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

Is equal to:

SELECT t1.* from HistoryTable t1, PartTable t2
WHERE t2.PartName = 'airplane' AND t1.PartRev = 'C' AND t2.PartNum = t1.PartNum

You can create these joins for your users, and show them they only need to
swap out the name.

-r


At 01:21 PM 7/27/01 -0700, Gonzo Rock wrote:

>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
> >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!
> >
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01