Re: primary key question

Answers intermixed with original post. People who belong to the C. J. Date croud will say yes to using a numeric key but I believe that systems should be designed to meet the needs of end-users and not theory.

In article <8bgh8t$e91$1_at_nnrp1.deja.com>, kal121_at_my-deja.com wrote:
> Is there any compelling reason(s) to use an artificially generated
> primary key, i.e. a sequence?
>
> I have a couple tables that have unique fields that would serve very
> well as primary keys, and would simplify some queries in the process.
>

It depends. Whenever the key can or needs to be the next available sequential number then a sequence is a good choice. But in may cases an attribute column of the table is the key from the point of view of the business and it makes much more sense to use it then to generate a meaningless number.

> This may seem like a dumb question, but I'd like to know because all
 of
> the schemas I have ever seen *always* use sequences as a primary key.
> Also, using alternate keys as my primary key in only some tables would
> cause some tables to use sequences as primary keys, and some tables
not.

I have seen both and neither method is superior to the other by its nature; they are just two different approaches to get to the same point.

> This causes me some vague discomfort for some reason. Maybe it is just
> aesthetics.
>

The key should be what works best for the business rules and design logic. One key might be a sequence to eliminate carrying three key columns to a child table while another is a single column business value familiar to the end-users. As long as each key is chosen with the business requirements in mind then the end result should work.
>

--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.