John,

>>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.

No, I don't feel like getting distracted by your games. The over-complicate issue is your observation, not mine. I Don't want to quarrel about these kind of issues.

To me it certainly not over-complicating or complicating the issues. I clearly see advantages and disadvantages to each approach. If you don't see them, thats a pitty, it does not change my standpoint.

>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.

Then reply to the list mentioned in my post to erik.

>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.

So, can you tell me which authority has written this rule ??? It certainly is not listed in documents regarding the relational model nor the SQL commitee. Please don't tell me humbug.

>>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 JimN mentioned it should be in a FPA issue a few years ago.

>As far as naming conventions, I would love for you or Jim to trot out a "hidden danger".

This topic has been discussed before. There are hidden dangers, especially when more than one developers worked on the product.

>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.

Well, I know my writtings are not known to be clear and understandable in the first place. It seems i've got to work on it.

>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 >...

It could cost you performance. See the link.

>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.

Which rules, and again where are those rules written ???

>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.

Who says, please prove it. Remember I do make a decision based on the circumstances, I'm certainly not advocating the FORCED use of intelligent keys.

>And no, not everyone used intelligent primary keys. We were using surrogate keys at MEI 7 years ago..< s >..

So, what about 10 years ago ???


>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 >....

hmmmm, Keep up the good replies John, you might find 2 or 3 people who find your replies tactical.

Walter,