Amy Blankenship said:
First, if you want a patient to be able to stay multiple times and to be
able to get information on those stays as the same patient, that
information cannot be stored in the same table as the patient him/herself.
Note I don't know what MRNum is. So I am leaving it out. If it is
directly related to who the patient is, put it in tblPatients. If it is
related to a particular hospital stay, put it in tblPatientStay.
No, we are only interested in the current patient stay. It's a database to
determine therapist assignments but the long term data is to keep track of
patients on therapy and how long they are in the hospital. We have
determined that patients who are on protocols (explained in more detail
below) get better faster and are thus discharged out of ICU sooner than
patients who are not on protocols. Now we want to expand it and get it into
a database to see if the patient's hospital LOS is also reduced and also
collect and report the data each quarter and thenby year. We now pull
patient charts after they are discharged from the hospital and enter the
info into a spreadsheet. This is very time consuming. Since our staff
already tracks most of the info on the current "flat" database then just
deletes the record when the patient is off therapy, my idea was to never
delete the records and our group could add the extra info after the patient
is gone into the new database. Some questions we would like to get
answered....Who is on therapy today and what is their location and name.
What doctors are ordering protocols and which ones do not. Are ventilator
patients in certain units getting off the ventilator sooner than patients in
other units? How many aerosol treatments were ordered and how many did we
do last week? MR number is Medical Record number and is assigned to a
patient on their first admission to the system by Admissions department and
is unique to that patient. Admit number is for the current admission and
the billing people use this so we need it listed. We would use the MR
number if we wanted to pull a patient chart or look up other admissions on
the hospital database. I do not use SS numbers because MR number would be
unique to that patient and there is no need for us to have the patients SS
number on our database. Our database has nothing to do with the
hospital/system database or electronic health record. It is soley for the
RT departments use.
tblPatients
=======
PtID
PtLName
PtFName
PtMI
PtDOB
PtSSN (I suspect you'll need this to distinguish patients from time to
time)
tblPatientStay
==========
PtID (fk to patients)
AdmitNum
AdmitDtTm
DischargeDtTM
IsolationYsN
This also seems to be a combination of what should be two tables. The
first is whether patient should be receiving therapy, and the second is
therapy actually received. Note that if you know when therapy is to start
and end, you don't need anything that tells you whether a patient is
actively receiving therapy, because any time when they are not supposed to
be receiving therapy, they would be inactive. Presumably when a therapy
comes up for renewal, they are either renewed, in which case the renewal
period would be the current therapy period, or they are not renewed, in
which case they are inactive. I'm leaving off PtTpyOnProtocols, because
it is not obvious where it belongs.
Any active patient is recieving therapy, we don't keep track of the other
patients only our respiratory patients. A patient may be on a ventilator,
aerosol treatment, etc to start with then the ventilator will be
discontinued and that ThpyType would be inactive but the aerosol Thpy would
still be active. Once a patient isn't on any therapy, I thought we would
make them inactive. Now that I think about it, there could be a patient
that is off of all therapy then a couple of days or even a week later be
reactivated so I need to think about how that would work too.
PtTpyOnProtocols is a yes/no field. Each type of therapy is either a
protocol (which the therapist is responsible for determining what frequency
they need or what ventilator settings they need) or not. We keep track of
this because we have stats that show that patients on protocols actually do
better than non-protocol patients and we need to keep tracking this. It is
being done by hand right now so the database will hopefully save some time.
Back to the pencil and paper. Thanks for bringing up the part about "should
be recieving therapy" and actually recieved. I need to work that into the
database somehow too because we could possibly get additional information
about missed treatments etc...
The above table should probably look like this:
tblPatientTherapy
PtThpyID
ptID
ThpyTypeID
PhysID
BeginOnDtTm
RenewOnDtTm
ThpyOrderNum
ThpFreq
tblTherapySessions
PtThpyID
ThpyEquipTagNum
SessionStartDtTm
SessionEndDtTm
TechnicianID
This one is better, but I suspect that since ThpyPoints is plural, it
should probably point to another table to allow it to relate to multiple
records. More than likely, you'll need to have a table of possible points
and a junction table between tblPatientTherapyType to allow you to define
the points only once, yet use them in more than one therapy.
ThpyPoints is actually not plural it is just one number we assign to each
therapy type to determine productivity and number of therapists we will need
for the shift. Aerosol treatments are given 2 points per treatment, MDI is
given 1/2 point if done on a vent patient, 1 point if done on a non vent
patient, vent checks are given 2 points etc... Each point is about equal to
10 minutes of time so we add up all the points for all floors to determine
how many therapists are needed for the 12 hour shift then we also do it per
area and determine how many therapists are needed for that wing or how many
wings one therapists will cover.
..
Thanks so much, I'll be working some more on it this weekend and probably
start a new thread with hopefully more clear intentions and slightly
different table structures
