>Why do you over-complicate the issue?

Because many people only look at the simple side of the problem. If you do, you easely get the impression that using surrogates everywhere are always the best solution. In fact there are both advantages and disadvantages on both sides which may justifiy the use of intelligent keys.

>
Walter, do you realize what you are saying? You just admitted to over-complicating the issue. You made my point. Over-complicating an issue is never an optimal approach. There is nothing wrong with looking at the simple side of a problem. In fact, the simple solutions are the most artful. Overly complicated solutions lead to over-spending and cost overruns.

As for advantages vs. disadvantages, I see no disadvantages to the surrogate key route - so long as you obey the rules. If you change the rules in midstream - to create some perceived disadvantage, then I suppose you could find some.
<


A given case might ask for an intelligen key. (look at the city case in my reply to chris)

>
Walter, again, you munge concepts. You are combining the concepts of primary keys with keys that have meaning to the user. Primary keys serve 2 primary purposes - both of which should be transparent to the user.
<


>If there is one thing you can hold constant, it is the methodology with respect to PK's.
>

Just like JimN mentioned there are hidden dangers in using 'consistency' in methodolgies. For example the DELETED() tag case, indexing every field, the naming conventions. In some points in time they where religion in the VFP world, but felt out of the sky at a given point. I do regard this surrogate key issue the same. When looking at the details of the difference between the two approaches you can only conclude that surrogate keys indeed do have many advantages. But there are also less obvious disadvantages, I tried to name a few (See one of my replies to Erik).


<
I don't know of anybody who has a clue that ever advocated indexing every field. As far as naming conventions, I would love for you or Jim to trot out a "hidden danger". Walter, while you manage to use a lot of words, there is a lot of repetition. Ultimately, you don't say all that much. One of the common responses to you is that the respondent does not understand what you are saying.
<



When both strategies have it's own advantages and disadvantages, its madness to force yourself to take sides. IMO, the only right way is to make a decision on a case by case basis. If I look at my most recent personal projects I'll see that about 70-90% I use surrogate keys or generated statical numbers.

<
Using surrogate keys in all situations costs me nothing. And FWIW, surrogate keys and generated numbers are the same thing...< s >...
<


Lets say I want this oderId as main identifyer for other purposes (eg. printing on the packinglist and/or invoice. Then it becomes an intelligent key (since it has a meaning outside the DBMS.

>
Right here, you have changed the rules. You only use a PK for 2 things. You clearly have an issue grappling with elementary relational db concepts.
<

>The issue you bring up is EXACTLY why the use of surrogate keys is a best practice. It is a practice that works in ALL situations.
>
Yes, that might be, but intelligent keys also work in all situations. The difference between the two lies in complexity and flexibility. Depending on the case you might choose for using intelligent keys (see city case). Untill about a few years ago everyone used intelligent keys.


<
Right, your approach is more complex and less flexible. And no, not everyone used intelligent primary keys. We were using surrogate keys at MEI 7 years ago..< s >..
<


Keep up the good fight Walter in spite of it being an excersize in futility. In your quest, you might find 2-3 people to agree with you...< s >....

< jvp >