Re: Help finding natural keys

Anton Versteeg wrote:

> 
> 
> Jan Hidders wrote:
> 

>> 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?
>
> Since patient id is already unique, firm id should not be part of the key.

Mr Versteeg

The reason behind having the firm_id as part of the key here is that an organization might run on its own database initially. It might then be merged into a database shared by another firm. When they are running in separate instances, the new patients can be created using (select max(patient_id) where firm_id = :firm_id), and when merged there will be no duplicates.

Same for questionnaires.

The completed_questionnaire has a primary key of firm_id, questionnaire_id, and completed_questionnaire_id.

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, questionnaire_id) references questionnaire );

insert into complete_questionnaire (

    firm_id, questionnaire_id, completed_questionnaire_id )
values (:firm_id, :questionnaire_id, 0);

update completed_questionnaire

   set completed_questionnaire_id = (

           select max(completed_questionnaire_id) + 1
             from completed_questionnaire as cq1
            where cq1.firm_id = firm_id
              and cq1.questionnaire_id = questionnaire_id
       );

The composition of questionnaire_id and completed_questionnaire_id is unique.

Alan Gutierrez - ajglist_at_izzy.net Received on Fri Jan 17 2003 - 19:07:45 CET