Re: Help finding natural keys

In article <nfMV9.89095$t06.4191316_at_news2.east.cox.net>, Alan Gutierrez <ajglist_at_izzy.net> wrote:
>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.

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.

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

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

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?

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

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"?

• Jan Hidders