Re: Help finding natural keys

Jan Hidders wrote:

> In article <nfMV9.89095$t06.4191316_at_news2.east.cox.net>,
> Alan Gutierrez  <ajglist_at_izzy.net> wrote:

> Just for the record let me state that the use of surrogate identifiers is
> a controversial issue. Even experienced database designers sometimes
> differ in their opinions over them. But what everybody does agree about is
> that even if you use them you still have have to also define natural keys.
> If you cannot find those, your data model is very probably faulty.

Mr Hidders

Thank you for taking the time to respond. I appreciate your consideration of my problem. I understand that this is a controversial issue. I've read much of the thoughts on this on both sides. I am asking that someone help me understand the natural key argument, since I seem to be unwilling to use natural keys as primary keys in my schema.

>>Have I just fallen into a bad habit? Are there candidate keys for things
>>like a person that I just cannot see? Below are some examples from my
>>database.
>>
>>create table firm (
>> firm_id integer not null,
>> firm_name varchar(32),
>> primary key (firm_id)
>>);
>>
>>The firm may change it's name, so that doesn't make for a good key.

> The only requirement for a key is that *at every moment* there are no two
> entities (tuples) for which these values are the same. Since the firm name
> will be unique before the change and after the change it is therefore a
> good key.

Okay. It might have to be a combination of the name and the state and country in which the organization is incorporated for it to be truly unique, however.

My concern here is that if I choose a name for a primary key, and the name changes, I am using the firm in identifying relationships throughout the database. If the name changes, should I really change the primary key, and all the primary keys that reference it. I thought changing a primary key was a no-no.

>>create table patient (
>> firm_id integer not null,
>> patient_id integer not null,

    first_name varchar (32),
    last_name varchar (32) not null,
    SSN varchar (32),
    sex char (1),
    ...
>> primary key (firm_id, patient_id),
>> foreign key (firm_id) references firm
>>);
>>
>>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.

> So what does patient_id exactly identify? A person? If the same person is
> taken into another hospital will her or she get the same patient_id? And
> if the same person is taken into the same hospital twice? And how can it
> be that you store absolutely no information about the patient except the
> firm? How are these patients identified when people in the hospital talk
> about them?

A person, yes. Pardon me, I omitted what I thought were obvious fields for the sake of brevity. I should have added an example and ellipsis.

Patients are referred to by name in these generally small organizations. Names not unique, of course. Since our customers are many small organizations, there is no standard patient id. Although I'm sure most of them use SSN for their billing applications, many of our customers still keep health care documents in paper files organized by name.

If a patient were to go from one hopital to the next, they would have to transfer patient records according to a procedure that ensures the patient's privacy. If we were to have two different health care firms in our application that shared a patient, the data would be duplicated. One firm must not be permitted to see another firms information.

>>create table questionnaire ( firm_id integer not null,
>> questionnaire_id integer not null,

    name varchar (64) not null,
    reassess boolean not null,
    labeled boolean not null,
    ...
>> primary key (firm_id, questionnaire_id),
>> foreign key (firm_id) references firm
>>);
>>
>>Much of the application data is stored in questionnaires. These map to the
>>many forms that are filled out and added to a chart, assessments, plans of
>>care. There is no unique identifier for these things in the real world
>>where they are photocopied Word documents designed by a nurse. Again,
>>select max.

> 
> Again, how do people in the hospital refer to these things when they talk
> about them. Do they say "give the questionnaire 123217ab" or do they ask
> for the "cardiogram that was made for mr. Doe on Januari 3 2003"?

Again, I've foolishly omitted important details for the sake of brevity. Questionnaires are named.

Alan Gutierrez - ajglist_at_izzy.net
http://khtml-win32.sourceforget.net/ - KHTML on Windows Received on Fri Jan 17 2003 - 18:24:10 CET