Re: NextNumbers Tables

This is exactly the way I try to do things, having a surrogate key and natural key with a unique index.

Ever tried tracing a transaction manually (as one is sometimes forced to do) when you only have a natural key consisting of 10 fields?

Pablo Sanchez <pablo_at_dev.null> wrote in message news:<Xns931FB07963045pingottpingottbah_at_216.166.71.233>...
> Alan Gutierrez <ajglist_at_izzy.net> wrote in
> news:Pine.WNT.4.53.0302111803350.1376_at_CX1198465-B:
>
> > A while ago, I was asking this group about natural keys in a thread
> > called 'help finding natural keys'.
> >
> > http://groups.google.com/groups?th=77167e54ea12e34f
> >
> > Using a natural key as a primary key has real world benifit. It
> > prevents duplication of data. Duplicates can have consiquences if
> > you are billing customers, or scheduling chest x-rays, or assigning
> > landing strips at Heathrow. If your customers are billed monthly,
> > you can use the month as part of the natural key, and you will be
> > ceritan that only one bill exists for that month. The chest x-ray
> > could have the time rounded to the quarter hour and location as part
> > of its natural key.
> >
> > If you are going to use a surrogate key, give thought to how your
> > application will prevent duplicates. You will have to write
> > procedural code, or add extra unique indicies. It will no longer be
> > a natural fact of your database design.
>
> There's nothing to preclude one from having a natural key and a
> surrogate key coexisting on the same table. You can implement
> UNIQUEness on the natural key to ensure that the business rules are
> not violated.
>
> The surrogate keys give you the flexibility to provide the layer of
> abstraction between operation/implementation and the business needs.
>
> As a side note, I might write a white paper on this, one business need
> is to provide efficient paging on a resultset. You can do this easily
> and efficiently when surrogate keys are used. This can all be done
> server-side.
Received on Fri Feb 14 2003 - 11:08:00 CET