Martin,

>>>I just can't advise anyone on something that I don't believe in.
>>
>>This confirms, my observations, that the choice between intelligent (or natural) and sorrugate keys is more of a religion than one of a science.

>>It seems that because your 'believe' you have not opened your mind for some very good reasons daniel has given here.

>No, no. Anyone who knows me can tell you that I'm everything but religious. I believe or not just in FACTS. For me, as I think I stated clearly here lots of times, are FACTS that maks my belief. I should say the same about your position, but of course I don't think so.

I've got a real problem with people declaring something as FACT. More than once i've seen migrate FACTs into general observations or less. All too often i've discovered that there exceptions to such FACTs. I think I should agree with JimB that using surrogate keys is a "best practice" but not an absolute rule. the term "best practice" leaves enough room to agree on the issue. Probably i would use intelligent/natural keys more often than most of us.

>I think that no one that went trough this discussion deserves to be called "close minded".

I said: "Narrow minded".

>>Two very good points:
>>1. If having a table with many (x) foreign keys, for reporting (when only adressing the value of those meaningfull keys) you'll have to include x joins to accomplish the same report with surrogate keys. This means:
>>A. Performance could be terrrible as the number of x goes up.
>>B. The table is far more readable when either debugging it, or presenting it to endusers in any way.
>>
>>2. When using static (single attribute) natural keys (like in the superbowl example) I see no problems arising here.

>1. I said many times that -for me-, even on this extreme cases where for some use (a single report perhaps), the benefits of using surrogates could easily offset any of the troubles.

I don't think so. In Daniels case the natural keys have not changed since at least 10 years and will not likely to do so in the future (and again whats wrong with an cascading update ??). I could imagine that performance would drop a to 1000 or more percent of the times need with natural keys. Please explain to the user ? Please explain to the IT manager.

Further, could you explain which troubles I would encounter. All my explained advantages have been debunked. Now it's time to discuss the advantages of surrogate keys.

>I am in the consistency camp -and not, I don't follow it religiously. You stated that you have something against consistency.

I've got nothing against consistency itself, as long as it is not missused. I strongly have got the feeling that consistency is misused most of the time.

If a table only contains natural keys, the table contains only intelligent values. Consistency ?

If using surrogate keys, you'll have the choice between integer enumerators, ID fields and GUID (16 byte character). The GUID enforces uniqueness of a key no matter on which server it is generated. When working with several servers replicating data, you'll like to run into problems with integer fields as two different servers might generated the same key for the same table. Then GUIDs get attractive. So one time you'll use integers and sometimes you'll use GUIDs, Consistency ??

Index all columns of a table. Consistency ???

An Index on DELETED() tag for rushmore optimization. Consistency ????

>I just don't. For me consistency is a goal trough design, coding, user interfase, and what the hell, even life. That doesn't mean to get stuck with a method or tool when you dicover it wrong. But as soon as some practise proves you helpful and its benefits when used consistently are more that some inconvinience that can cause you, I prefer it. It's a matter of balance for me.

You've made a decision on what is most convienient for you. For another developer it might be totally different.

>Of course, balance makes that sometimes you need to deviate from your consistent method because it doesn't pays off, I recognize it. Just don't think is the case for surrogates.

And that's my friend, is your opinion.

>>Your experience might tell you, you've never encountered problems with using surrogate keys. My experience say's I have enjoyed using natural and intelligent keys for the last few years.

>As I said, I can't counter YOUR experience. And I don't pretend you to advise on mine, neither.

So ? We'll have to respect eachothers standpiont on this.

>As we (I refer to me and my company) started to propagate the use of surrogates, this was an inconvinience because some people where no very technical and saw joining as something a bit complex. Today is just the same for us. Having enjoyed the many benefits, anybody cares about having to type a little more. And never felt that performance was an issue (except in those cases where something was actually not optimized and need to be fixed anyway).

>>I think, we both have two differente relgions, and as always with 'religions' it's hard, if not impossible to convince the other.

>I think we don't have religions, but different viewpoints. You are VERY analitycal, which is very good, even if at some point someone can think that you overcomplicate thinks.

I'd like to drill down into every detail (which of course is impossible), and draw my conclusions on this experience. I only declare something as a FACT when it is mathimatically proven. 1 + 1 = 2. that's proven. But I'll still have to see that using surrogated everywhere is the best practise in any case.

>>We'll have to respect eachothers 'opinion' and as time passes by we will see both are points to have merit.

>Of course. It has being my point since the begining.
>I'm glad we finally reached an agreement in that.

Agreed,

Walter,