Jeff,
You are tackling one of the hardest jobs that database designers can
attempt. What you are trying to do is very complex and involves lots
of medical standards and even more laws and regulations. Do not even
attempt this project until you familiarize yourself with the HIPPA
compliance requirements.
Then there is also the issue of your own personal liability. Very scary.
Suggestions:
Seek legal advice from someone well-versed in HIPAA compliance
Consider one of the many off-the-shelf solutions for this task (even
FileMaker ones!)
Get a really big liability policy
Seek a developer who has created an app like this before and hire her
to consult/mentor on the process
Others' suggestions may not sound so dire, but this is a serious
project and there are really dramatic stories circulating about how
even the likes of IBM and Microsoft beat their heads against medical
records apps and got serious headaches.
BTW, I consider myself a fair developer and willing to develop just
about anything that a customer would pay for and I would not dare to
create such a project from scratch. (Maybe if I could hire a team of
medical app programmers, perhaps.)
j.
On Jan 3, 2009, at 3:24 PM, Jeff Mar wrote:
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]
> 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]
http://richardsrussell.livejournal.com/
= = = = = =
Cheops's Law: Nothing ever gets built on schedule or within budget.
--
Jonathan Fletcher
FileMaker 9 Certified Developer
Project Foreman
NewMedia Construction Co.
[email protected]
Instigator
The BB&J Network
The "Go-To Guys" for
FileMaker Development in Louisville
[email protected]
