Question Design of a database

[Zorkmid]

Hi there,

Im am building a new database to keep track of infections and infection precautions.

I am trying to decide whether to keep track of multiple infections and date ranges in a single record or to separate one patient's infection precautions into more than one record.

If I were to keep multiple infections of a single patient in one record, is there a way that I can calculate how many days they were covered by a type of precaution.

For example:

Fred was in the hospital twice:
- Once he suffered from infection A and was under Contact precautions from april 10th to april 20th.
- He later was admitted and was under droplet/airborne precautions from april 25th to may 7th.

ALSO: during this time a patient may have been transfered to another unit

So I'm looking for a way to log all of this information:

-Unit patient was on
-Precautions
-Date Range
etc.

And be able to change it around without altering a "master table" that contains the patient information.

It's like I need "subrecords" (if that makes any sense) for every time a single patient is admitted, or moved from one unit to another.

I will also need to query this information to report statistics such as:

-Total isolation days per unit
-Total isolation days by pracaution type
-Total isolation days per patient (for financial purposes)
-Total active isolations nightly (for cleaning staff density)

I will start doing my own research and building a template DB. I think this is a very interesting project and I look forward to increasing my knowledge base as I attack it.

I am also looking forward to hearing your ideas and opinions about the project. No comment is too short!

-Thanks,
Z

 

[Kafrin]

If ever you feel you need 'subrecords' then what you actually need is a new table. I would recommend storing the 'precautions' info in a separate table. Patientsrecautions will be a 1:Many relationship. The precautions table will contain the info about the ward and dates specific to that precaution.

 

[gemma-the-husky]

i'm sure you need more than 1 record - this sounds really complex to me - and needs some initial thought.

you need to analyse your data to decide exactly what tables you need. I am sure there is some standard for medical cases

something like

patients
consultations (1 patient = many consultations)

is a start - but from the consultations you get diagnosis and treatment

now it seems to me that
a) a consultation can result in several problems needing resolution BUT ALSO
b) a diagnosed problem will result in several consultations, possibly connected, but possibly independent

you then have to also consider how cases, treatments, prescriptions etc etc fit into all this.

You are going to have to manage an assortment of many-many relationships, I feel.


I would be inclined to google this to see if there is any ready made analysis - I think this is a particularly speciallised area, and definitely needs to be structured correctly.

Rabbie in another post mentioned he wrote a veterinary system, which is clearly similar - perhaps he can offer some insights.

 

[Zorkmid]

Thanks Gemma and Kafrin,

Just to be clear, this database would only be used by infection control practicioners, who are support staff in my organization. It's purposes are to help manage the resources that deal with the isolation of these patients and effective communication between front line staff and housekeeping, also for statistical purposes.

I have also just realized that infection control would like a way to write free text on these cases to eliminate some paperwork.

Right now, several excel spreadsheets are being used to keep track of all this, with each practicioner responsible for a group of areas in their personal excel sheet. So when a patient is transferred they must migrate that patient to another sheet/practicioner.

Thanks for the advice, I will seek out Rabbie's post.

-Z