Re: Normalization, Natural Keys, Surrogate Keys

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?

> 2. Everywhere I go people are trading off performance over everything
> else. Ok, I admit I'm a little touchy on the subject, but 2 of your 3
> points were directly related to performance. In my limited experience,
> I've seen more projects fail due to 'understandability' than
> performance.

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?

     customer_tbl.cust_id = address_tbl.cust_id.

Need to see some of the account stuff?

     customer_tbl.cust_id = account.cust_id

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.

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.

> 3. Hymns in database discussion groups. Now that's definately
> violating some integrity consraints!?
>
> I'm keen to learn more, so how can your 'surrogate key' version
> communicate the same thing as a composite primary key? I now have to
> examine the parent/buesiness rules to realise what it's 'real ' key
> is, since you've replaced it with a surrogate key. BTW, I'm not going
> to argue about the performance issues, although database vendors could
> probably create their own surrogate keys internally to replace the
> composite key.

I would assume most do, but they would still need to store the uniqueness criteria as well.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.