>Jim, how do you handle uniqueness and not deleted()?

First I should say that I don't understand that "FOR NOT DELETED()" has any significant impact on performance, specifically if one has the same expression without the FOR clause as a regular index.

My first option is to try to avoid duplicates at entry time by informing the operator and preventing writing in the first place.
In cases where a deletion is proper users are told that deleted records are not recoverable. Then I change the front of the TAG value(s) to be something that is guaranteed to be at the end of the index. A simple function tailored to the situation can ensure non-dupes there.

Finally I should note that I've never faced a situation where virtually all the fields of a many-fielded record form parts of the (compound) key and that does seem to present a situation worthy of considerable thought.

Basically, you do what you gotta do that feels right to you (assuming that user-friendliness is a critical part of the "feels right' < s >).

cheers

>
>Jason
>
>
>>Houston,
>>
>>I've read the other replies to the time of this writing...
>>
>>My preference ("best practise" for me but not necessarily everyone else) is to restrict my usage of surrogate keys in favour of the "natural key" from that business' point of view.
>>
>>I do this for one simple reason - when the user confronts a problem they themselves have a reasonable chance of 'connecting the dots' to describe EXACTLY what seems to be happening. To me there's nothing worse than telling a user 'that's what it looks like to you but it actually doesn't work like that within the program' or something to that effect (maybe even 'well it's far more complicated than that in the program'). There are also other advantages )in my opinion) for 'natural keys' over (always using) surrogate keys.
>>
>>In the case cited I can see a need for the compound key, and I'd say that even if I used a surrogate key I would also use exactly that key in that circumstance (as candidate), if only as added assurance that it was unique through the data. And if there were cases where the individual components of the compound key were useful too then I would have TAGs on them too.
>>
>>I feel that each case needs independent review unencumbered by generalizations (pseudo axioms) because applications are differentt, businesses are different and people in those businesses are different.
>>
>>
>>>In Message #796671, Jason Mcfarlane has indicated that he is having problems with slow updates, with one table in particular, which has the following indexes:

Primary Key(Primary)
>>>location_id + depot_code + DTOC(delivery_date) + route_number ;
>>>      + load_number + Str(stop_number, 5) + customer_number ;
>>>      + order_number + product_code + how_requested
>>>not deleted()
>>>
>>>location (regular)
>>>location_id
>>>
>>>Primary (regular)
>>>location_id + depot_code + DTOC(delivery_date) + route_number ;
>>>      + load_number + Str(stop_number, 5) + customer_number ;
>>>      + order_number + product_code + how_requested

I personally strive to avoid compound keys. From a performance / functionality perspective I see no difference between say a SQL Select with the following WHERE clauses:

cField1 + cField2 = cVal1 + cVal2
>>> and
>>>cField1 = cVal1 AND cField2 = cVal2

The first is predicated upon a compound index, the Tag can only be used in this single context. The second method has the advantage that each Tag can be used independently or in combination with other Tags. In systems that I have designed compound keys are the exception and not the norm, I would be extremely unlikely to see the same field included in several different tags and being effectively indexed over and over and over (I see 3 instances of location_id above). Does any one else have any thoughts on indexing best practices.