Re: Help finding natural keys
Date: Fri, 17 Jan 2003 17:48:25 GMT
Message-ID: <JlXV9.90966$t06.4328375_at_news2.east.cox.net>
Bernard Peek wrote:
> In message <gaWV9.90626$t06.4312699_at_news2.east.cox.net>, Alan Gutierrez
> <ajglist_at_izzy.net> writes
>>Bernard Peek wrote:
>>
>>> In message <nfMV9.89095$t06.4191316_at_news2.east.cox.net>, Alan Gutierrez
>>> <ajglist_at_izzy.net> writes
>>
>>>>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.
>>>>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.
>>>
>>> You often need to use generated IDs for people. Hospitals will have
>>> their own patient or case numbers, you might be able to use those.
>>
>>Many of our customers are very small organizations that don't have one
>>true case number in place. Their current system is paper in a manila
>>folder.
>
> Take a look at the documents in that folder. How does someone know which
> folder to put a document in?
Mr Peek
Thank you for your response. This discussion is helping me immensely.
The surname on the folder tab. These are heath care workers scribbling on paper. They refer to their patients by name.
The organizations are small, and even in our large organizations the charting is done by small facilities. This is charting, not billing. Patient numbers are not in use here. A good thing. I'd say that a doctor that referred to me as Q123.456 has got to work on her bed-side mannor.
>>We plan on offering this product to many small organizations. The burden >>of adopting an organization specific patient key for each 30 patient heath >>care facility doesn't sound appealing.
>
> No, I'm glad it's not my problem. Each of those organizations will have
> their own coding system but it's worth talking to them about codes that
> might be used in all of the sites.
>
> If you don't have a suitable coding system then you will probably have
> to create a free-text field for the code that the facility uses. The
> risk there is transcription errors. If a facility asks for data about
> "Q123.456" and you have "Q123-456" you won't be able to respond.
Again, we've called the patients and the workers by name to date, such that when I am on the phone I can browse through the directory in the application's UI and find the patient or health care worker in question. (We are still in development however.)
With this in mind, I've not struggled to find a natural key suitable for use as a primary key in the case of patient. If there are two John Does in the emergency room (likely) the health care workers are going to identify the patient by injury, not by a patient id.
Alan Gutierrez - ajglist_at_izzy.net
http://khtml-win32.sourceforget.net/ - KHTML on Windows
Received on Fri Jan 17 2003 - 18:48:25 CET