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

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.