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.

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

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

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.

>I don't care how much you change the structure, a very simple methodology overcomes the issue you bring up.

Which methodology and which issues ?

>To illustrate, once a table has been named, once the db has gone into production, it is fixed. So in the case of an orders table, the PK would be an auto-incrementing integer named orderid, orderpk, or whatever you wish to call it. The point is that no matter how many columns you add/remove, the PK remains the same.

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.

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

>To have a way of handling PK's in one scenario and another way of handling PK's in another scenario is to uneccessarily over-complicate the issue.

Maybe I try to get the max out of every situation. If I conclude that a intelligent key might be better in terms of flexibility, performance, readability etc, I will use it.

>And as such, cannot be a value-added excercise. And, if a change-over from one way to another is necessary, not only is it NOT a value-added excercise, it is a value-detracting excersise.

>The only point I agree with you is that shit happens. Yes, db structures and specs change. Development does not occur in a theorhetically-driven utopian vaccuum. That means however that ones methodlogies must be able to overcome and adapt to the point that unproductivity is minimized. And IMO, what you preach is the antithesis of this principle.

I've got a real problem with strict following methodologies. each methodology has its value that it describes the global guideline on which you can reach your goal. For the beginners methodologies are a real help, because they can learn about the global aspects that play a role in this.

But when you want to achieve more and trying to get most out every situation you'll discover that there are exceptions everywhere that may justify to do different than the methodology describes. There are methodologies that were disasterous because they didn't work at all. If you look at SDM (system development methodology) you'll see that many other methodologies derived from it and do not have few of the many disadvantages SDM has.

I tend to search for every weak spot in methodologies and take them into account when appropriate.

Walter,