Re: Non-unique index and normalization

"Pablo Sanchez" <pablo_at_dev.null> wrote in message news:<N6Zz8.70$VQ6.152721_at_news.uswest.net>...
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CD03D05.6A430B90_at_exesolutions.com...
> > John Hunter wrote:
> >
> > > Is there a normalization rule that indicates that there must be an
> unique PK
> > > key on every table in the database? I am having a debate with a
> co-worker
> > > and one of us says that there has to be one and the other
> disagrees. Who is
> > > right?
> > >
> > > Thanks,
> > > -John
> >
> > While it may exist in theory, Celko and others can answer that far
> better than
> > I, no such rule exists in practice.
>
> I agree, no 'rule' exists in practice but all tables, unless there are
> duplicate values in the table, have a 'default' PK: all the columns
> in the table.
>
> The 'normalization rule' that defines that a table should have a PK is
> 3NF. Here's an excerpt from An Introduction to Database Systems, 3rd
> Edition - C.J. Date (Yes, a very old copy! :)
>
> A relation R is in the third normal form (3NF) if and only if, for all
> time, each tuple of R consists of a primary key value that identifies
> some entity, together with a set of mutually independent attribute
> values that describe that entity in some way.

My experience tells me that in the real world not every table needs or should have a primary key. Some tables hold records of events or transactions and no meaningful unique identifier exists. The rows are basically created as something happens and accumulated in no particular order until being batch processed where they are usually deleted or moved to a history transaction table. Adding a numeric generated PK serves no useful purpose as it will never be used in processing. If for some reason you need to identify specific rows you can use the rowid.

The majority of tables in a well designed system will have a PK, but some tables may not.

IMHO -- Mark D Powell -- Received on Thu May 02 2002 - 07:52:10 CDT