Hi john,

>Of course you knew I was kidding about not listening to you, right? Hence the smiley?

Right.

>OK, in your scenario - with no non-data bearing PK, what happens when the 1001 account becomes the 1002 account for administrative purposes? Big PITA to track down and change all possible 1001 transactions and ledger entries, whereas, with a non-data bearing key *nothing* needs to be changed except the chart-of-accounts.

Yes, this is indeed a big advantage for surrogate keys. However, one might ask the question, if the program should allow such changes. In a number of cases, you don't want to change a PK, because lots of printed documents rely on the PK being absulotely stable. I can imagine what chaos would arise when someone changes an articleno from "MINERAL WATER" into "ILLEGAL RAT POISON". I'll bet the sales manager would get a cardiac arrest when seeing monthly sales.

In other situations you might rely on the RI code to do a cascading update. The RI rules should be carefully defined, Just like cascading delete rules should be carefully defined.

>Now, if you want to keep transactions with the old account number when the account number changes (admitedly, some do), then you do want to bend normalization and carry the account number into the child tables and show it as it was when the record was generated...but it's still a single change to a parent record.

I won't call this bending normalization. This has to do with the design of the database. You want to track historical data.

But I wonder... What if the rules say that I may not change an account number since something is booked on it ?? With intelligent keys this is quit simple (An Ri update restrict rule). With surrogate keys this is somewhat more complex: You'll have to write some code to check this. This gets particular interesting when having multiple tables holding an accountnumber and beeing in a multiuser environment where local workstations read buffers are not refreshed yet.

Walter,
>And, BTW, I don't have keys go out of synch. Ever. Since VFP data does not support internally managed Identity type of integer fields, I always use some variant of SYS(2015) and SYS(0) or a GUID.
>
>>>Glad I could help. BTW, don't listen too closely to Walter :-) the extra joins you'l have to do to report on the data will be well worth it.
>>
>>It all depends on what you need. You know i'm not a big fan of surrogate keys, though I use both in my current projects. All I try to express is that it is unwise to blindly follow the GURU (This is always a bad advice, anyways) and always implement surrogate keys. I've identified a number of cases where surrogate keys are bad. One big disadvantage is that in general you need extra joins. When searching for ultimate performance, the last thing you want is extra joins. IOW there are cases where surrogate keys pays of, but the're certainly situations where intelligent keys hit the butt of surrogate keys.
>>
>>Another big disadvantage is that you've got to take care of generating the PKs. This is relatively easy if they're assigned in one place, then take 32 bit integers. But when talking about distributed systems where records can be added at different nodes without direct communication, you'll have to revert to GUIDs. The disadvantage of using GUIDs is the enormous space waste. a 16 byte character is not my idea of keeping my tables and application optimized.
>>
>>And what does happen if your PK table for some reason go out of sync and tries to generate values that already exist in a table? Please don't say it won't happen, because I've had lot's of trouble with this one. Under certain circumstances, when doing things like conversions, additions to tables, recovery etc, it is so easy to forget you've got to synchronize the PK tables. The users is confronted with a PRimary key uniqueness violation, while he can't do anything to solve the problem (because the surrogate key is not visible). With intelligent keys this problem is easely solved by changing the intelligent key in the inputform.
>>
>>The argument that it is a lot easier to change the intelligent key when using surrogate keys is relative. In most situations you don't even want the intelligent key to change. And in the rare circumstances that you want to do this, you can do a cascading update relatively easy (just as easy as a cascading delete) in which performance is rarely an argument.
>>
>>I don't understand, why people don't THINK about such issues. It certainly is not that difficult to see that both surrogate and intelligent keys have different properties and that its usage depends on a given situation. People who say that surrogate keys are always the way to go, have clearly not thought about the issue throughly. In the real world there are numerous examples to find of good working database systems who uses both strategies.
>>
>>I challenge anyone to find an high performance accounting system that entirely relies on surrogate keys. I doubt if one exists.
>>
>>Walter,