Re: Normalization, Natural Keys, Surrogate Keys

"Tobin Harris" <comedyharris_at_hotmail.com> wrote in message news:ac1g9u$mbrrp$1_at_ID-135366.news.dfncis.de...
> 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.

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
manageable
> 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
point
> 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
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?

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.

Thx!

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts