Re: Help finding natural keys

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Tue, 28 Jan 2003 15:48:25 GMT
Message-ID: <dDxZ9.45922$GX4.1729674@news2.east.cox.net>

Paul Vernon wrote:

> One small tip Alan. Call things by what they really are. You have already
> said that:
>

>>  The patient entity does not really represent a human, it
>> represents a patient's chart

>
> and
>

>> A patient is really a patient dossier, and that dossier belongs to a
>> particular firm which will be held accountable for it's contents.

>
> If a patient is not a patient, don't call it one in you model. Just
> because the business uses a certain term, don't expect it to be an
> accurate reflection of the actual concept. Getting your names right is one
> of the most difficult things in a data model, almost as hard as getting
> business folk to use a common standardised vocabulary when they discuss
> what they want a business system to do.

I have a patient table, with a name, ssn, sex, etc., and it really does model a patient. I was noting that the application models patient charts and that two firms may share patients, but they don't share charts. So, I do have a patient table, and it is not a misnomer, but each firm is responsible for their own particular row in that table. If firm A puts the wrong diagnosis in that table, then firm B should not have to be a co-defendant the lawsuit.

> Also, if you can't find a natural key for an entity and need to use a
> surrogate, think about putting all the other columns in an alternate key.
> That will at least stop any completely duplicate meaningful data.

Good idea. Thank you.

I am going to have to worry about data duplication. If I had a natural key as a primary key, that wouldn't be so. This part of the natural keys as primary keys I understand.

> Lastly, I can't remember if someone mentioned this, but surrogates are
> great when your possible natural keys only identifiy a thing at a given
> moment. A room number might identify a patient at a point in time, but if
> you don't have a surrogate, then keeping history with changing natural key
> values would be a real pain.

Yes. Actually, most of my tables have a version as part of the surrogate key. Something I kept out of the disucssion, since I really was interested in natural keys, but the requirement is that a questionnaire be reproduced just as it was when it was submitted, so if the patient's name was misspelled at admission and subsequent corrected, the admission questionnaire should reflect the error.

Thank you for your response.

Alan Gutierrez - ajglist_at_izzy.net Received on Tue Jan 28 2003 - 09:48:25 CST