Retitled from "Re: Go To related record"
On 2009 Jan 3, at 12:45, Jeff Mar wrote:
Hello. I am new to this but here goes...please let me know if this
is the appropriate forum.
I am building a database to track clients in a small medical clinic
(200 standing patients, 100 new patients a year - outpatient and
day treatment). A few design questions:
1. The clients will each have a unique medical record number in a
table (MR table). This will be connected to name and other info
that doesn't usually change (pt id table). Then, there is info
that may change more frequently....address&phone, contact info,
insurance info, emergency info for instance. Then there is info
that changes daily...labs, vitals, physical exam. For a medical
record, clinical info needs to be dated and signed so the daily
stuff I see as each type having it's own table and own entry. The
MR table obviously should be unique. But, the occasional changing
info, would you recommend separate tables? or one table with lots
of fields? My business partner has suggested we basically keep
track of all the info input i.e. all old addresses, old insurance
info, etc so we can track things backward.
I have built small databases but not larger databases and therefore
I am not sure if this still counts as a really small database in
terms of number of records. i.e. 200 outpatients among 5 staff may
generate 2 visits for 52 weeks so 200 x 2 x 52 = 20,000 plus 16
patients per day x 250 days = 4000 daily entries, 4000, md notes,
4000 staff notes, 4000 labs.
Would you recommend separate or combined tables? Would you create
a new record for each change in each table every time someone makes
any change?
This is the ideal forum for such questions, Jeff. In fact, I'll bet
that almost everybody on this list is grateful to see someone asking
these questions in the design phase of the database, instead of
waiting for a couple of years to try to resolve a flock of problems
arising from flawed original design.
I have 5 fundamental pieces of advice:
(1) Never destroy data.
(2) Normalize the living daylights out of this puppy.
(3) Never destroy data.
(4) Timestamp absolutely everything.
(5) Never destroy data.
I think that one thing you'll find you want is an overview screen for
each patient that will pull in all of the MOST RECENT data for her or
him. For sure this would include things like address, phone number,
insurance numbers, height, weight, most recent visit, next scheduled
appointment, next of kin, etc. Hiding behind almost all of these
things is a long string of less-recent data that you should be able
to call up with a click.
You'll also have to spend more time than most database designers
working your way thru security and confidentiality issues. For
example, you'll want your receptionist to be able to look at and
revise addresses, phone numbers, appointments, etc. but probably NOT
diagnoses, prescriptions, and so on.
There are a zillion other considerations on this project, but there
are also a flock of experts on this forum, and they'll all have
something to say. I expect most of them will contribute a bite-sized
piece like the above, so you can digest them in small, non-
overwhelming installments.
= = = = = =
Richard S. Russell, a Bright (http://the-brights.net)
2642 Kendall Av. #2, Madison WI 53705-3736
608+233-5640 • [email protected]
http://richardsrussell.livejournal.com/
= = = = = =
Cheops's Law: Nothing ever gets built on schedule or within budget.
