on primary keys (moved from Maypole list)

[prev] [thread] [next] [Date index for 2005/02/02]

On the maypole list, an extended discussion of its 'feature'
of singularlising plural names of columns and tables wandered into
a discussion on naming in general and MCPK, MCFK vs 'just a SERIAL'.
This is more appropriate to this list...so here we go...

On Wed, 2 Feb 2005, Perrin Harkins wrote:

> On Wed, 2005-02-02 at 17:23 +0100, Johan Lindstrom wrote:
> > Having said that, we all know that there are databases out there which
> > _have_ tables with composite primary keys, and IMHO it would be nice 
if
> > CDBI could deal with that also.
>
> Doesn't CDBI deal with that already?  Or are you talking about foreign
> keys?

In my thinking you don't really need the MCFK. You get your relational
integrity by having each individual FK relate back to its original table.
So you have an identifier for e.g. tray but of course coatings can be on
trays or bolts (and if some coating makes no sense or isn't available for
a bolt then that's a CONSTRAINT on the bolt table) etc. . So you have each
of these columns separately in your e.g. Invoice table.

It does start to get awkward at first look. After all an invoice just
wants a PN , description, quantity , unit and extended price (discounts go
in there as well somewhere). It would seem that if you but put a pure PN
in your life would get so much easier since if they now start selling ,
say, foam rubber in addition to all those bolts and such you have a
headache. Or do you anyway because after all how do you assure referential
integrity. The MySQL crowd of course say "you don't", "the database 
doesn't care" and "deal with it in your application".
Those of us using real RDBMS , where the R stands for Relational , like 
PostgreSQL , Oracle , Sybase etc. , do care about referential integrity.
So we want our library to care too :-)