Help finding natural keys

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.

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.

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.

Alan Gutierrez - ajglist_at_izzy.net Received on Fri Jan 17 2003 - 06:10:43 CET