Re: Help finding natural keys

In message <nfMV9.89095$t06.4191316_at_news2.east.cox.net>, Alan Gutierrez <ajglist_at_izzy.net> writes
>I've been reading this group and considering the design of my database used
>for management of patient charts.
>
>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.
>
>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.

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.

>
>create table patient (
> firm_id integer not null,
> patient_id integer not null,
> 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.

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.

>
>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
>);
>
>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.
>
>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.'

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

In search of cognoscenti