Re: Normalization, Natural Keys, Surrogate Keys

> We don't lose that knowledge per se, it's just a bit indirect via the
> surrogate key. What I clearly failed to communicate is that we keep
> the natural keys but we don't use them as our primary key.

Ok, you said that your surrogate keys and your natural ( sometimes compostite? ) keys can co-exist in the data model, so you were fairly clear there. It sounds like your surrogate key do add indirection, but I imagine you're trading off over a few (crucial) issues such as development ease and performance (am I right here?). I'm a developer too, and I must admit I'd cringe a little if I had tow work with composite keys 16 columns wide.

> > 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.
>
> On its own, you cannot. But I don't believe I stated that you could.
> It's a level of abstraction that we as developers can easily map
> between. It's not that complicated. I'm working on an application
> that has 450+ tables with this concept implemented. Of course the
> number of tables means nothing but just trying to give an example that
> it does work.

Ok, I'll accept that. You say that the level of abstraction is manageable and I assume, practical. I'll admit that I've never worked on a system that consisted of more than 80 tables, so I'm willing to accept at this point that if everyone is happy with your way of working then you're probably on to a good thing.

This might be a stupid questions, but I'll ask anyway. If ANSI 2014 compliant sql provided you with a mechanism to use the abstraction you have 'built in' to your schema, would that be a good thing? For example, if you could join on composite keys without explicitly stating that in the sql, and without any performance drain (assuming vendor implementation) then would you go with this?

I'm probably hoping that you'll say "yes" here, but I'd like a realistic argument. I still think that we break theoretical 'rules' because of performance and practicality. So, if the tools allowed us to design for theoretical correctness, but work with practical simplicity, without performance hinderance, would we forget about compromising on design?

Tobin Harris

> > 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. Which
> would
> > remove the performance probs. (I'm thinking on the fly here, so be
> gentle)
>
> Some do (Oracle's ROWID) and some don't. Oracle's ROWID's are re-used
> though so it's not exactly as you're intending. It's definitely
> possible but at the end of the day, being that I want to control
> everything, I rather know about data placement than let the RDBMS
> handle it 'behind the scenes' for me.

> Thx!
> --
> Pablo Sanchez, High-Performance Database Engineering
> mailto:pablo_at_hpdbe.com
> http://www.hpdbe.com
> Available for short-term and long-term contracts
>
>
Received on Thu May 16 2002 - 18:47:23 CDT