Designing a hospital visit database

[Mark M S]

I need some help with some basic design considerations to captures
variations of visits in a hospital of the same patient.

I am using Access XP ( 2002) and developed an application to capture newborn
care - the initial history and physical and follow up visits. I did this
storing the information in a single table. (I used many supprting tables to
describe each element of the exam but basically this was single table that
supplied the information for the daily report.)

I am sure that wouldn't work if I kept multiple variations of the same
information for the same patient if I decided to record the individual
elements on a daily basis rather than for one visit. I had completed the
progress note before by creating a single memo field that store daily
progress notes which were a narration that I typed and I pasted into a
progress note memo field. I would just paste a new note at the end the the
prior so it would be one long string of text.

This design worked well until I decided that the same detail I used for the
History and Physicial was going to be necessary for the progress notes too
(which previously was a memo field that I just typed basic information)

Now I wish that each follow up day I could implement the detailed exam of
the heart and abdomen etc in dropdown choices for multiple days for the
patient.

I imagine that I must change my design to add additional detail visits. It
is just unclear to me how I need to redesign my tables to store multiple
encounters and so that I can query and report subsequent visits.

Now I have a base table in database0.mdb that that is linked to another
database that has the foms, queries, reports, and supporting tables

Database0 has
FirstName
LastName
BirthDate
Length
Weight
Gravida
Para
Heart
Chest
HandP
ProgressNote
etc.

Database1
tblHeart(contains i.e. murmur, dysrythmia, normal) for the entry form
tblChest (contains i.e. symmetric, retractions, etc) for the entry
tblGravida (1,2,3,4,5, etc) for the entry form
(forms, queries, and reports)

What information would you need to know to give me some guidance?

*************************************************
Mark M Simonian MD FAAP
Medical Director, ChildNet Medical Assoc.
681 Medical Center Drive West #106
Clovis, CA 93611
(559) 325-6850
www.markmsimonian.medem.com
****************************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.

 

[david epsom dot com dot au]

If all you want is a progress notes table, just create
that table, and add a field to it pointing back to the
relevant patient details record.

Create a relationship between the two tables.

Add a subform to your patient details form to capture
the progress notes records.

On the other hand,

The relational database model can easily represent either
one record with many values, or many records with one value,
but not both.

That is, if you have one patient record for each visit,
you can have one address for each visit, or you can have
one current address, but if you try to associate all the
addresses with all of the records, you will have to record
extra data to determine if an address was invalid and corrected,
or changed, and which address is associated with which
patient record.

If you need to record multi-dimensional data like this,
just accept that validation and reporting and enquiry will
be difficult. You need a join table, but don't try to look
for a magic best way to structure your data, because there
is not one.

(david)

 

[Mark S]

Thank you David for your thoughtful comments.

I knew it would be complex. I am using an electronic medical record in my
office now and it is composed of many tables whose relationships and
structures are hidden from me. I wish it was portable and this question
would be mute. I suspect that I will need to look at maintaining some basic
table of the description of the patient and then create a daily record based
on their patient account number that is unique for their hospital stay. That
would be the link for the entire hospital stay then create reports by the
date or day in the hospital like admit, day 2 discharge day etc. I would
then create a form subform that linked that would allow my entry
information.

I can't think of another example of a similar model that I might use a
template of so I could short cut the detail of the table structure. I will
take a stab at this.

--
*****************************************
Mark M Simonian MD FAAP
681 Medical Center Dr West #106
Clovis, CA 93611
(559) 325-6850
www.markmsimonian.medem.com

 

[Guest]

There are several types of information that you are looking at, and there are
ways to relate all of them.

First, you need patient information (stuff that doesn't change, like name,
birthdate, sex, etc.), and you need information that you get during a visit
(physical exam, vital stats, tests ordered, test results, etc.). These
belong in two different tables.

tblPatient
PatientID (PK)
PatientStuff (all those little details, etc.)

tblVisit
VisitID (PK)
VisitDate
VisitTime
VisitStuff (again, all those little details, etc.)

However, you need a way to relate these tables. You can't do it directly,
because you have many patients, and many visits. So you use a bridging
table, that will match up patients with visits.

tblPatientVisit
PatientVisitID (PK)
PatientID
VisitID

You go into relationships, and match tblPatient.PatientID with
tblPatientVisit.PatientID, and then match tblVisit.VisitID with
tblPatientVisit.VisitID. This will form the relational link that you need.
You can then put as much detail as you want into tblVisit, with links to
other tables that detail various medical stuff that you want as drop-downs in
your forms. You can now filter for all of a patient's visits, to use for a
report or form. If you use subforms, you can step through all of the
different visits that relate to a patient.

See if this format will work for your needs.

 

[BruceM]

I think a junction table would be needed if each visit could have several
components (exam, treatment, tests, etc.), or if each visit could be with a
different provider (doctor, therapist, etc.), or if visits somehow involve
multiple patients (workshops, etc.), or any number of other scenarios.
However, I don't think it is needed to associate Patients with Visits as
such. Each patient could have many visits, but each visit is (I assume)
associated with a single patient. Or am I missing something?
Wouldn't be the first time.

 

[Guest]

You do make a good point, in that a visit usually involves one patient at a
time. However, the point about a visit could be with someone other than the
doctor themselves is good, since a doctor may wish to keep information about
consultations or various therapists also. Each Patient Visit could possibly
branch off to other tables, which could itemize special topics such as diet,
therapy, consults, etc., which wouldn't have to always have an entry for the
Patient Visit, but would be there to link to if necessary. So, you could have

tblPatientVisit
PatientVisitID (PK)
PatientID
VisitID
DietID
TherapyID
ConsultID
LabWorkID
etc.

The VisitID would just be information that the patient's doctor would
gather, and other tables could be for other types of information gathered at
different times than the doctor visit. They wouldn't have to be used, but
would be available, probably as drop-down selections. That would be a handy
way of filtering various types of information for forms and reports.

There are nearly always several ways of approaching a problem. The best way
is to visualize what information will be needed from the database, and then
plan the tables to make it easy to enter and filter the data. After the
database is created, you will need to enter data, and you will want to
retrieve data. Make it so that the database does as much of that work as
possible.

 

[BruceM]

There are lots of twists and turns to a database of this sort. Each visit
could involve several providers, each provider's portion of the visit could
involve any number of tests or procedures, and so forth. Thinking about a
database is sometimes the trickiest part.
Back to Mark's original question, a pencil and paper sketch or flow chart
sort of thing may be useful as a starting place. Also, I don't see that
there is a reason to use a different database for some of the tables and for
the forms, etc. There may be good reason to split the database, but that is
a different matter.
Also, Mark, be aware that this is a public forum. Your e-mail address and
other information are now widely available. I recommend a fake e-mail
address, or one that needs to be parsed by a human, such as:
(e-mail address removed)
(which is not only disguised, it is fictional)

 

[Mark S]

I appreciate such a thorough review of my considerations. My databases in
the past have not been as complicated as a true electronic medical record
must. That is why I bought one and did not build one. I am expert first at
medicine and only dabble in Access to get some routine data collection that
there was no specific tool to do.

The reason why the table was split is because of security. In previous uses
of a simpler database the primary table sat on a SQL server and was
maintained (secured) by the hospital and my front end was sitting on two
different workstations in the hospital. No data resided on the workstations
but was on a secure server.

I was asked to look at this because neonatologists (doctors who care for
premature infants) wanted to create a legible record the way I create with
my newborn reports they viewed when I sent them patients. THey knew that
systems that could do what I was doing could cost tens of thousands of
dollars and I might do something for them without any cost. (It is just fun
building databases) I am sure that I bit off more than I could chew to do a
professional job but some tool might allow them a much better tracking tool
and cleaner record than they had before and no dictation would be needed.

Let's see if I can digest the comments and start putting this into action.


*****************************************
Mark M Simonian MD FAAP
681 Medical Center Dr West #106
Clovis, CA 93611
(559) 325-6850
www.markmsimonian.medem.com

 

[LMB]

Mark,

I hope you continue because I am an RT who is learning to develop access
databases and I sure can follow the medical terms and how they relate to one
another better than, orders/customers/vendors/parts etc....Good Luck, I'll
be following your posts if you continue this.

Linda