Re: Help finding natural keys

--CELKO-- wrote:

AG>>> In the real world, patients are identified by name by someone who
> is responsible for a dozen or so patients. <<
>
> Maybe the name and the name of the responsible person form a key?
>

>>> These health care organizations use questionnaires designed by the nurses,
>>> usually in Word ... questionnaire is a model of a blank questionnaire, it
>>> is meta data, a completed questionnaire is an instance of the
>>> questionnaire ... a questionnaire can have sub-questionnaires ...  <<

> Arrrgh!!

That's what I needed to hear...

> This is looking like more of a problem than we can do in a newsgroup
> thread ...

...I wanted to make sure that it wasn't as simple as it sounded in some of these debates.

In the archives I'd read that the lack of well defined natural keys indicates a weak model. This was the cause of much hand-wringing on my part. It seems that this is not a hard and fast rule. It is reassuring that people are asking me the same questions I asked myself to determine natural keys, and that the answers are not forthcoming. SSN, for example, has even more caveats than I'd imagined. Illegal immigration? My goodness. Better to learn that here than in production.

That the application is to be shared by dozens of small organizations makes the choice of natural keys that much more difficult. Some organizations will naturally identify a patient by first name, last name, and to break a tie, room number. Home health care organizations won't have rooms however. In emergency situations, the name might not be known at all.

I better understand the costs of a surrogate key (maintianence, an extra index, etc.), and I am prepared to pay them. I will reconsider how these surrogate keys are generated.

Those questionnaires are working by they way. The customers love them. (Although, we're not in production yet.)

Alan Gutierrez - ajglist_at_izzy.net Received on Sat Jan 18 2003 - 21:00:46 CET