Hi Jeff,
I'll just speak to the patient demographic (unchanging, e.g. Date of
Birth, names of family members) and briefly mention a few more.
Clinical management databases start with the demographic. The family
address details are a concern if this a pediatric practice. In that
case tracking multiple addresses may be important to the business
rules of this office. Otherwise, the records are patient-centric; it
is the patient's address details that are germaine. And it might be
quite appropriate to track the legacy addresses and different phone
and emergency numbers of the patient, so the individual's history of
address and phone numbers in tables may indeed be appropriate.
However I think the family address details are peripheral unless this
is a family practice, or a psychologist's office, where family
members have to be contacted from time to time.
Even in doctors' offices, sometimes less is more.
As far as the other data tables; Visits go in a separate encounter
table (BP, etc). Lab results in a table related to encounters. I
consider this a pretty small and simple project.
Four more points.
- To track the type of encounter (1st admit or outpatient) you can do
the numeric as you were considering, but in the end your users would
prefer if you give legible labels. And, not a trivial point, from
experience, leave the MRN alone, and don't combine it in
calculations. Sooner or later you will need to mask it, and any calcs
you have designed around it will similarly need to be masked.
- Create a complex unique key for each patient, not their MRN. Use
something like an alpha character (P for example) & (non-modifiable)
Serial number & GetAsNumber(CreationDate) & GetAsNumber
(CreationTime), or use a more sophisticated approach. Whatever you
do, never use the MRN as a primary key. The same principle applies to
the other tables.
- Be prepared for when they ask you to report on all the patient,
encounter and lab data without names, MRN's or DOB's. Non-modifiable
Serial numbers are handy in these situations, but don't confuse them
with keys.
- Never pull the solution you have created onto a laptop, or install
it on your home computer if it has any real patient data. If the
solution ends up on your computer, immediately do the drill: Purge,
insert dummy data, trash the live version and make sure you empty
your trash _securely_.
Best wishes,
Beatrice Beaubien, Ph.D.
i2eye, Toronto, Canada
FileMaker Business Alliance
On Jan 5, 2009, at 24:25:12, Corn Walker wrote:
On Jan 4, 2009, at 11:00 PM, Jason L DeLooze wrote in sum:
Sometimes de-normalization is a good thing.
Clearly the weariness is affecting Jason tonight. While his
assertion that the portal may be meaningless has merit (after all,
a shared address does not define a family unit) this does not imply
de-normalization is the cure.
Indeed the structure Richard suggested - a single Address record
with an association table linking individuals to that address -
provides exactly the same opportunities a de-normalized structure
might. The trick then lies in the presentation. Will the user be
changing the address data directly or will there be an intermediary
interface to manage the creation and updating of address data? The
first option, direct address record manipulation, is subject to the
flaws Jason raises. The second option need not be.
However, let's be clear: when you associate people to an "address"
what we're really talking about here is a location. Harvey Summers
can be reached by post at location "2 GE Road Building 37B,
Schenectady, NY 12345". The "address" serves as a label for the
location, which in turn is a hierarchy of locations.
What Jason alludes to is a larger problem which is primarily an
issue of semantic carelessness and ill-considered application
design. For the first part, when someone provides us with an
"address" we often do not stop to consider what the nature of the
address is. This properly begins with the address request: why did
we solicit the address in the first place? Is it a residential
address? An address at which one receives their post? The type of
address being solicited is more important than the address itself
because the type will impart meaning to the data which, lacking
such, is nothing more than a location (Box 1234 in the 06776 Post
Office in New Milford CT). When we receive an "address change"
simply having the new address does not generally provide enough
actionable information. Perhaps we can infer the type of action to
take through comparative analysis of old and new addresses ("PO Box
1284 New Milford CT 06776" might indicate a data entry error) but
it is more often the case that we simply need more information.
What type of address is this for Harvey (obviously not a
residential address)? Has his residence moved? Does he have any
family members that receive their mail (or reside as the case may
be) at the new address with him? The simple raising of these
questions illuminates the insufficiency of the simple data model
originally proposed. It also implies that there is a thing called
"family members" which may or may not be independent of the
residential and correspondence addresses.
The database designer, not having seriously considered the domain,
often errs in the presentation of address-related information and
does not put in the effort to create the data entry mechanisms that
might invite the above questions. As a result he colludes with the
organization in soliciting and storing information that is of
dubious value even before entering the database.
That is to say, normalization has little to do with any of it. I
can envision the same type of interface necessary for proper
location updates stored in a non-normal form and reliant upon
scripted updates to modify the location information for related
persons. In fact, the "DRY" principle doesn't much apply to "person
x is located at location y" information" and storing locations
proper may not be within the scope of the system design. Ultimately
the data architecture question needs be answered by a thorough
investigation of the requirements, not an a priori presumption of
how to properly structure and store "addresses."
Food for thought...
Cheers,
-corn
Corn Walker
The Proof Group
http://proofgroup.com/
