Re: NextNumbers Tables

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.

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com