Re: Help finding natural keys

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.

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.

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.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Sun Jan 19 2003 - 22:06:01 CET