Richard, That is very helpful for me in terms of deciding to keep
data. For the forum
1. What does normalize mean in this context?
2. If each time I add a piece of data, I create a record to time
stamp, then should I have a field that keeps track of "active" or most
recent change and set the old record to inactive?
3. If I am making multiple iterations of a record with a single change
in it, should I use smaller tables (fewer fields) or bigger tables?
Does this kind of efficiency (smaller tables mean less duplication of
other data) even matter? The smaller tables require better design to
pass the info back and forth so more work up front than just a big
table of demographics.
4. Will definitely work on the overview screen with most recent data.
5. Do you consider using complex ID's? i.e. 00123.00101 so MR is
00123, with the .001 meaning 1st admit and the .00001 meaning
outpatient (vs IOP vs Full day treament)? This passes three pieces of
info and then I can use calculations in foreign tables to break it
down into three different pieces. It had seemed like this might at
times be useful for go to related records as it would differentiate
each admission by sorting on a single field and yet I could use the
calculated result as a find field when I want by medical record
number. This may be more complex than just scripting finds to include
multiple criteria.
Thanks for the help.
Jeff
On Sat, Jan 3, 2009 at 11:42 AM, Richard S. Russell
<[email protected] <mailto:[email protected]>> wrote:
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]
<mailto:[email protected]>
http://richardsrussell.livejournal.com/
= = = = = =
Cheops's Law: Nothing ever gets built on schedule or within budget.
