Re: Normalization, Natural Keys, Surrogate Keys

"Galen Boyer" <galendboyer_at_yahoo.com> wrote in message news:uit5n9t61.fsf_at_rcn.com...
> On Fri, 17 May 2002, comedyharris_at_hotmail.com wrote:
>
> > 1. By making the parent id a foreign key, it seems like you're losing
> > information in the design. More specifically, a child is related to a
> > parent, but not dependent on it for it's existance. So here, the
> > business requirements are not well comunicated through the design?
>
> I'm don't understand how this is different with natural keys?

The example that Pablo gave was looking at replacing a natural composite key with a single surrogate key (whilst still keeping the composite key columns in, but with a unique constraint rather than a primary key constraint). I'm just saying that the composite key is useful for indicating that there are dependencies between entities. For example:

Customer

Here we can see that an order doesn't just have to be related to a customer, it can't exist without the customer existing since the customer_id is part of it's primary key. I agree that this approach is questionable though. If the order_no is unique, then this could be made the PK, with a unique constraint put on customer_id and order_no. One approach is to opt for a surrogate key if there are more than one combination of columns that could be a composite key.

> I've found that most developers, me included, know nothing of the
> natural keys (at least when we start our projects). The people that
> know the natural keys are the client. If I start off the project with
> the design philosophy that each table has a single surrogate key and its
> name is very similar to the table, then as we move through the project,
> when we talk entities we can immediately envision and code the SQL. We
> need to join customer_table to address_table?

I don't know. The votes still out with me, but I'm not sure I like this this 'automatic' use of surrogate keys. I suppose the main reason for me is that I associate it with sloppyness, which might be a little unfair. I've seen people create autonumber keys everywhere because they can't be bothered to do any analysis, and they just need a key on the column so they can write their next line of code. However, I *can* see that there are clear benefits to this practice, but providing you do it as a design decision rather than a lazy kludge!

> We then keep the natural keys around for defining the actual unigueness
> and for display in the app so the customer can understand what she is
> seeing.

This sounds reasonable, do you put unique constraints on your 'candidate' natural key?

> But, all of this surrogate talk would mean nothing if we couldn't come
> up with the natural key when asked. The end user really matters. I
> just like to try and make it as easy as possible to get the end user a
> solid app in the time frames given. Lots of keys required to make a
> join happen is just another roadblock in the way of achieving that goal.

Hmmm. It becomes a little inpractical. I always think 'how many times am I going to write this SQL?' Usually, once it's done, it's done. If you use views, you may only have to write your awquard joins once for selects. Incidentally, I've also use the surrogate key approach sometimes, but I'm just getting opinions as to whether it's the best way.

Tobin Harris

> Galen deForest Boyer
> Sweet dreams and flying machines in pieces on the ground.
Received on Fri May 17 2002 - 11:58:35 CEST