How to record contnuous variables over time?

[akerenyi]

Hi there,

Many of you might already be familiar with my amateur questions regarding the database I'm currently developing for our medical research group.

We are working at a neonatology ward, an intensiver care unit for newborns. These babies recieve continuous hourly physiological monitoring, infusions/drugs, respirating and frequent test (blood, US, etc.) for their first week of life.

My current problem is with recording the continuous variables, like infusions. A sample data for one patient would look like this:

Morphine infusion started at 15:00 on 15/11/2011, and the speed is recorded in the documentations every hour, until it is stopped at 18:00 on 18/11/2011. (There is a big printed spreadsheet for each day and each baby, columns correspond to hours, rows to infusions). The speed of the infusion might be changed a few times while it's running.

Originally I thought about making tables for each drug, where the fields would correspond to hours. This is probably not the best way to do it, regarding normalization, but I can't figure out a better one.

If anyone had any constructive ideas, I would really appreciate it.

Thanks a lot,
Aron

 

[spikepl]

Your issue is not clear. From all the normalisation babble, I believe you did get the point that time is a data item, just like what you record. If you record one thing at a time, then you have a record of that with the time it got recorded. If you record more than one thing in one go, then you have fields in your record to accommodate that, and the record is still tagged by one time.

 

[gemma-the-husky]

no - that is the way to do it

you have to structure the data to follow the real world model

so it sounds like you should have a table linked to the patient recording all the observations for that patient

this will be 1-many. you would need a time field in the subtable - and you wouldnt limit yourself to 1 per hour - just record the time of the ob, and whatever data you need to record.

 

[akerenyi]

So you're all suggesting that I make

tbl_Infusions
- ID
- PatientID
- Time
- Morfine
- Saline
- other infusions

Thus, each record would correspond to 1 hour from one patient's care. This would create approx. 200 records / patients, with over 300 patients that's 60 000 records.

 

[spikepl]

with over 300 patients that's 60 000 records.

You make it sound like an issue. It isn't.

 

[gemma-the-husky]

yes, access can handle millions of records

the thing is, though, that you need to consider how to interrogate and analyse that number of records - because clearly you cannot do it all by inspection

 

[MarkK]

I would do tables like this...

tInfusion
- InfusionID
- Substance (Morphine, Saline, Benzene, Grenadine, ...)

tPatient
- PatientID
- Name
- ...

tPatientInfusion
- PatientInfusionID
- PatientID
- InfusionID
- Time
- Volume
- Duration

The main point being that Morphine and Saline are data, not field names; not structure. If some super new infusion is developed you want to be able to infuse that too without redesigning your tables.

 

[melissa_may1]

Hi Aron,

How's this project going?

I think that LagBolt has given you the best plan, setting up tables as he suggested. (Though in addition to Benzine and Grenadine he forgot to add Chocolate Syrup)

You want to have this set up so that the entries can be made at any time, and can refer to any medication.

As you stated, at this time the infusion data is once per hour, that could change in the future. Medications may need to be adjusted more often or new infusion pumps may be data-capable, and able to send data directly to the database every minute.

Don't worry about the number of records this will create. As SpikePL mentioned, 60,000 records isn't an issue. If the database grows significantly, you always have the option of moving it to SQL Server.

If this is all planned and operating well, an eventual move to SQL Server will be more easily accomplished.

Good Luck!

 

[akerenyi]

Thank you all for your kind help. I have followed the structure suggested my lagbolt.

I am making the final strokes now on the tables structure. As soon as I'm done, I will post a brief outline and I would appreciate your comments on it.

Than I will probably also have some questions regarding forms