Re: Help finding natural keys

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Fri, 17 Jan 2003 16:27:56 GMT
Message-ID: <gaWV9.90626$t06.4312699_at_news2.east.cox.net>

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.

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.

>>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.

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.

>>create table questionnaire (
>>    firm_id integer not null,
>>    questionnaire_id integer not null,
>>    primary key (firm_id, questionnaire_id),
>>    foreign key (firm_id) references firm
>>);

>>create table completed_questionnaire (
>>    firm_id integer not null,
>>    questionnaire_id integer not null,
>>    completed_questionnaire_id integer not null,
>>    primary key (firm_id, questionnaire_id, completed_questionnaire_id),
>>    foreign key (firm_id) references firm
>>);
>>
>>Here is an instance of a questionnaire. A questionnaire might be completed

>>a number of times during a patient's care, like a progress report form. >>Again, select max.

> I don't see any reason for separating questionnaires and completed
> questionnaires. Why not use one table with a boolean field 'completed.'

The questionnaire is a model of a blank questionnaire, it is meta data, a completed questionnaire is an instance of the questionnaire. A health care worker can complete a particular questionnaire more than once for a patient.

This is part of a larger schema, and I didn't want to be rude with a huge post. A questionnaire contains asked_questions, with related offered_responses, chosen from a pool of questions with related responses. This is because a question can be asked on more than one questionnaire.

(Actually, a questionnaire can have sub-questionnaires, and so it becomes a parts explosion just like the menu problem of mid-December. I've been following the tree discussions. Eagerly awaiting the new tree book.)

This brings it together with patient:

create table patient_completed_questionnaire (

    firm_id integer not null,
    patient_id integer not null,
    questionnaire_id integer not null,
    completed_questionnaire_id integer not null,     primary key (firm_id, patient_id,

                 questionnaire_id, completed_questionnaire_id),

    foreign key (firm_id, patient_id) references patient,     foreign key (firm_id, questionnaire_id, completed_questionnaire_id)

                references completed_questionnaire

);

I kept the patient id out of questionnaire since we might want to complete questionnaires related to something other than patients.

Alan Gutierrez - ajglist_at_izzy.net Received on Fri Jan 17 2003 - 17:27:56 CET