Hi don,

>Well, I have run into my first difficulty with using surogate keys. I have a browse screen (grid) which I would like to allow the user to view ordered by expense category. However, the actual category description is in the categories file and not in the expenses file. Only the primary key of the category is in the expenses file. Very common scenario. So I need an index key such as upper(categories.descrip). However, when I create this TAG and open the form it informs me that the alias CATEGORIES does not exist. My environment has the categories file in it so there is an issue with the order in which those files are opened , etc ... blah blah blah .... Am I heading for trouble with this type of index/tag ?


You're right, this is not easier with surrogate keys. It is not reccomended to add such indexes to tables as you're asking for troubles. The most easy way out is by using sql-views and order them on any particular column or index the view.
However, views have the disadvantage that they:

1. Don't display the most recent data (it's really a snapshot).
2. Take considerable amounts of time to load.

Walter,




>
>>Hey Walter,
>>
>>Of course you knew I was kidding about not listening to you, right? Hence the smiley?
>>
>>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.
>>
>>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.
>>
>>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,