New database

[Linda RQ]

Hi Guys...My first question is should I create this in Access 2003 since we
will be getting that installed in a few months? We will still have Access
2000 on the company PC's for probably at least a few more years.

All of the table IDs are set as autonumber Primary Keys. If you see other
issues besides the items I am confused on below, please let me know.

The purpose of the database is to give out shift assignments, have a way to
collect patient data later on. Right now they have a flat database that has
worked for over 10 years but when a record is deleted, it's gone and we
can't collect any trends etc....The other thing is they use little symbols
like an * to designate if a patient is on protocols or not and if they are
they don't enter a date in the order date field....Many other issues too but
I don't want you guys to faint.

My confusion comes when I have a patient that is on several therapies and
maybe 2 vent therapies. Will that work?

The other thing is, could I just have one therapy table and include the vent
therapy types in there. The reason I didn't is because some of the vent
therapies have to have an equipment number...but now that I think about it
all of them don't either so perhaps I need 3 types of therapy tables. The
other reason is that the therapys all have medications with them, so I know
I need a medication table too but wanted to resolve these issues first.


PatientID
PtThpyID_fk
PtVentThpyID_fk
PtLocID_fk
PtMRNum
PtAdmitNum
PtLName
PtFName
PtMInit
PtAge
PtActive (Y/N)

tblPtLoc
PtLocID
LocID_fk
PtLocStartDate
PtLocStartTime
PtLocEndDate
PtLocEndTime

tblLocation
LocID
LocRmNum
LocBdNum
LocArea

tblPtThpy
PtThpyID
ThpyTypeID_fk
PtThpyStartDate
PtVentThpyEndDate
PtThpyStartTime
PtThpyEndTime
PtThpyOrderNum
PtThpyFrequency
PtThpyOrderOrderingPhys
PtThpyOnProtocols? (Y/N)
PtThpyActive? (Y/N)

tblThpyType
ThpyTypeID
ThpyType
ThpyPoints

tblPtVentThpy
PtVentThpyID
VentThpyTypeID_fk
PtVentThpyStartDate
PtVentThpyEndDate
PtVentThpyStartTime
PtVentThpyEndTime
PtVentThpyOrderNum
PtVentThpyFrequency
PtVentThpyOrderingPhys
PtVentThpyOnProtocols? (Y/N)
PtVentThpyActive (Y/N)

tblVentThpyType
VentThpyTypeID
VentThpyType
PtVentThpyPoints
VentThpyEquipNum

Thanks,
Linda

 

[Jeff L]

Hi, Linda. You're off to a good start. A few things I noticed that I
might change are:
1. Your Patient Table only allows for one type of Thpy and VentThpy.
You said that a patient could receive two types.
2. I think if I were setting this up, I would have tblPtLoc,
tblPtThpy, and tblPtVentThpy related to your patient table via the
Patient ID, not the foreign keys in the Patient Table. Again, you are
limiting yourself to one type, but then what happens when a patient
receives 2 types of Therapy? The relations I am suggesting would take
care of that problem...Many Therapies to one Patient.

These are just suggestions, so take them however you wish.
Hope that helps!

 

[Jeff Boyce]

Linda

In a fast glance through, I noticed that you (repeatedly) have start date
and start time (and end ...). Access has a date/time data time, so you
don't need to store these separately.

You've listed a table structure with field names, but I'm not in your line
of business, so I have only a rough guess of what kind of data you are
storing in the fields.

If the very first list is your Patient table, then you have some further
normalization to do. That first list includes fields that seem like they
are treatment-related. A well-normalized "Patient" table would ONLY have
person-related info (name, address, DOB).

Your table named "tblPtVentThpy" has no PatientID field (a foreign key).

I'm guessing your data structure would benefit from further normalization.
Consider sitting with paper and pencil and sketching out all the "entities"
(things about which you want to store data), and then all the
"relationships" (how the entities are connected). For example, you might
have:

<Persons> (your Patients AND your Physicians could be in this list)

<Diagnoses>

<TreatmentProtocols>

<Patient-Diagnoses> (each patient can have one/more diagnoses)

<Patient-Treatments> (each patient can have one/more treatments)

By the way, if this is a US-based system, there are some very tough new
regulations (HIPPA) governing what and how you can gather/store medical
information...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tina]

that's HIPAA, Jeff - a common mistake. the full name is Health Information
Portability and Accountability Act.

hth

 

[LMB]

By the way, if this is a US-based system, there are some very tough new

regulations (HIPPA) governing what and how you can gather/store medical
information...

Jeff,

I have been chasing my tail with this one for weeks. I have contacted our
IS department, no return call in 2 weeks. I placed an IS request yesterday,
I don't expect to hear anything for at least a week. I can't find anything
on the web, so far, everything I have found is an ad to by companies, secure
faxing services, webspace etc...The info on our portal is geared towards
paperwork, conversations and not anything about the datastorage on a
computer. Maybe you can save me some time if you think what I describe may
not be in compliance, then I can drop the project and probably find another
job with IS. We currently have and have been using for around 10 years a
flat database with all the info below on a "secure" M network drive. Only
RT's have access with their own login name and password that is changed
every 3 months. The only function of the database is to print out a report
at each shift change with assignments for respiratory therapists. When a
treatment or ventilator or patient is discontinued, the record is deleted.
I have no idea what type of server our data is on but I think it was Novel
and recently got changed to Micorosoft. I think the server is located in
another city but that may be the backup server. I know for certain none of
the info is on the C drive of any PC, we can't even access our C drives. I
just started in this new position to help organize the staff educators and
managers because I "know the most about computers" created a couple other
simple non patient related databases that have helped out our department and
am interested in doing it. I have been a registered respiratory therapist
for 22 years but would like to switch to a role like this one and let the
younger therapists run to the code blues and give the treatments.

Reports of new orders from our Siemans Electronic Health Record, physician
order entry system print off in our supervisor's office every 4 hours. The
supervisor then enters the info from those reports into our "Flat" RT Flow
database and prints off a report every 12 hours at shift change.

For work assignments, we need to keep track of patient names, room numbers,
therapy with medication to deliver, ventilators, bipap and also dates of
therapy re-evaluations due. For the data collection part which we can't do
with the current database because records are deleted, we need to keep track
of which doctors ordered the therapy, which doctors ordered protocols and
which doctors didn't, which protocols reduce ventilator time the most, the
start and end times of therapy and ventilators, the ventilator number so we
can prove that it did not any nosocomial infections and so BioMed can locate
a ventilator without putting out an APB. I have not even included the
possibility of billing from this database but it may be something I want to
add. Some of the info can be obtained from our EHR but what I described
above, we have to manually collect and enter into the system.

We do not need demographics and nothing is lost as far as patient records
because all orders, demographics, and procedure info is stored in the EHR.
Pharmacy has a system called Admin Rx which the therapist scans the patient
band, scans the medication bar code, scans their own badge....the time and
treatment info etc is stored in this system and is "safe". Now you are
probably wondering if we have these wonderfully sosphicated programs why
can't they just give us the info..Well, they do not talk to each other, and
at this time there is no one to "run" certain features so they are
unavailable including the billing feature and custom made reports. We can
get some printed reports but then we would still have to compile and report
the data in a way that is meaningful to us. As far as EHR, the system is in
the final phase of getting rolled out at 3 hospitals. Initial roll out
started with us and lasted a year and a half, the 2nd hospital was done all
at once last month and the final hospital is going live in November. So,
the IS resources are taken up with creating ordersets, keeping up with the
constantly changing medical practices, medications and just generally
working hard to get computer illiterate healthcare workers up to speed and
trying to get the physicians to enter their own orders. There will be no
reports approved for at least a year.

Back to the HIPPA issue....I probably should have asked that first...Does
anybody in here know if what I described above is OK?

Sorry this was so long but it's kind of complicated and I really appreciate
you bringing up the HIPPA thing because I have been trying to make sure this
was ok but I need to get a start this as you can see, it is going to take me
a while to get it normalized. I also thank you for the other suggestions, I
think I'll be able to concentrate better in the morning.

Linda

 

[LMB]

argh...I do that constantly, I think HIPPA is more natural <g> I need to add
this to my spelling checker list.

 

[LMB]

Oh..yea, the PtID would go in my PtTherapy Table. I'll draw that out and
see if it makes more sense.

Thanks!

 

[Jeff L]

It wouldn't...Who's to say that a patient can't have more than 2? What
the liklihood of that happening is I don't know since I'm not in the
medical field. But why limit yourself? In designing a database, you
should make it so you can easily add the number of entries that you may
need and not be restrictive. If that means you only add one item for
the person then so be it, but if for some reason you need to add 100
items then you can easily do that as well without complication.

 

[Jeff Boyce]

I guess I always confuse the big, bloated, grey-skinned pile of regulations
with the "water horse"...<g>

Jeff

 

[Jeff Boyce]

I stand corrected on HIPAA. I'm not a health care provider and do not have
any leads for you on having your design evaluated for HIPAA compliance.

I'd urge you NOT to rely solely on your corporate IS department to
understand (and be able to communicate) HIPAA compliance issues.

I'll point out that Access has a fairly steep learning curve. If you are
looking to create a comprehensive, user-proof application, using MS Access,
you have your work cut out for you.

Best of Luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[tina]

ROFL!

I guess I always confuse the big, bloated, grey-skinned pile of regulations
with the "water horse"...<g>

Jeff

 

[tina]

i agree with Jeff that you shouldn't depend on your IT department to
interpret and enforce HIPAA regulations. there should be a person or entity
(department, board, etc) that is charged with interpreting, implementing,
and enforcing HIPAA compliance in the facility you work in. if there isn't,
or you don't know one way or the other, suggest you start by asking somebody
in the medical records department; if anyone knows who is "in charge of
HIPAA compliance", those folks should - though HIPAA issues are not
restricted to how medical records are stored.

also suggest you google on "HIPAA compliance". i immediately came up with a
lot of hits, starting with

http://www.cms.hhs.gov/HIPAAGenInfo/
that's on the US Dept of Health and Human Services official website.

hth

 

[LMB]

Thanks, tina. I spoke to both medical records and IS security mgrs.