Re: Help finding natural keys

In message <gaWV9.90626$t06.4312699_at_news2.east.cox.net>, Alan Gutierrez <ajglist_at_izzy.net> writes
>Bernard Peek wrote:
>
>> In message <nfMV9.89095$t06.4191316_at_news2.east.cox.net>, Alan Gutierrez
>> <ajglist_at_izzy.net> writes
>
>>>I am generating keys using select max. I've considered what Mr Celko says
>>>about how this is just a sequential file model in SQL, but I try as I
>>>might, I can't think of good, er, natural keys for my entities.
>
>> In the UK every corporation has a registration number which is unique and
>> unchanging. It's just an integer ID that's created by the registration
>> authorities. If you don't have a code like that which you can trust
>> completely then creating an ID code is the best you can do. You will need
>> to build in some form of checking to ensure that it isn't possible to
>> enter the same company twice, even if it has another name.
>
>Mr Peek
>
>Thank you very much for your feedback. It is greatly appreciated.
>
>I am in the United States. I suppose I could use a Tax ID number, but that
>might change if the organization is absorbed by another organization.

That sort of infrequent change is relatively easy to handle. As Jan Hidders says in another post, you could use the company name as a key. I think there are pragmatic problems with that.
>
>One of our customers is a firm that is growing by absorbing other firms,
>which may or may not adopt the name of the parent firm, so at some point I
>might have to model the corporate organization/hierarchy as well. This makes
>choosing a natural key doubly confusing for me.

You need to think about what level of detail you need to record. It might be sufficient to add a group table too.

>
>>>A patient is a charge of a particular hospital so, I select the max
>>>patient id for the hospital to get a new patient id.
>>
>> You often need to use generated IDs for people. Hospitals will have
>> their own patient or case numbers, you might be able to use those.
>
>Many of our customers are very small organizations that don't have one true
>case number in place. Their current system is paper in a manila folder.

Take a look at the documents in that folder. How does someone know which folder to put a document in?

>
>We plan on offering this product to many small organizations. The burden of
>adopting an organization specific patient key for each 30 patient heath care
>facility doesn't sound appealing.

No, I'm glad it's not my problem. Each of those organisations will have their own coding system but it's worth talking to them about codes that might be used in all of the sites.

If you don't have a suitable coding system then you will probably have to create a free-text field for the code that the facility uses. The risk there is transcription errors. If a facility asks for data about "Q123.456" and you have "Q123-456" you won't be able to respond.

-- 
Bernard Peek
bap_at_shrdlu.com
www.diversebooks.com: SF & Computing book reviews and more.....

In search of cognoscenti