Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Normalization, Natural Keys, Surrogate Keys

Re: Normalization, Natural Keys, Surrogate Keys

From: Pablo Sanchez <pablo_at_dev.null>
Date: Thu, 16 May 2002 20:24:11 -0600
Message-ID: <3ce467f6$>

"Tobin Harris" <> wrote in message news:ac1g9u$mbrrp$
> 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

Hmmmm, I never thought about it as a trade-off ... it was more of a gain as a developer as well as a DBA. <g> What I mean here is that I like having the work stratified between the business, the developer and the DBA. This is also why I'm a firm advocate of stored procedures. But I digress ... :)

> Ok, I'll accept that. You say that the level of abstraction is
> and I assume, practical.

Very much so!

> 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
> that if everyone is happy with your way of working then you're
probably on
> to a good thing.

Everyone? Yikes!

Personally, in the projects I've worked on from scratch, it's been a very good thing. Is it right for everyone? Dunno. I haven't found any faults in practice. But frankly, even with the number of years I've been doing this, I don't think it's a statistically valid sample. Yeah, I guess I sound like I have no spine. What I'm intending is to be approachable and if something better/else comes along, I'd love to hear about it so I can incorporate into The Method [tm].

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

Interesting. I guess in order to say yes/no, I'd need to see what exactly was intended when we joined two tables? Are we specifying the '16 columns' from one table to another to join them? If so, that seems like code that would be a nightmare to maintain. And if someone is hacking in the middle of the night, I'd like to make it as easy as possible for them to grok the code in front of them -- which they may not be familiar.

It's my belief that adding surrogate keys doesn't convolute to the extent of making the design lost in obfuscation. Again, just my experience ... not saying it's right or wrong but it certainly has worked well in the project I mentioned.


Pablo Sanchez, High-Performance Database Engineering
Available for short-term and long-term contracts
Received on Thu May 16 2002 - 21:24:11 CDT

Original text of this message