Re: Help finding natural keys

Jan Hidders wrote:

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

Sorry, I disagree: names are IMO not good for primary keys. Often they are not unique and it is easy to enter wrong names (typo's etc) and thus
wrong keys.

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

Since patient id is already unique, firm id should not be part of the key.

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

If questionnaire id is unique then firmd id should not be part of the key.

>
> -- Jan Hidders

--
Anton Versteeg
DB2 Specialist
IBM Netherlands