I'm creating an application that as its core function tracks various data over time (blood glucose levels, insulin dosages, food intake etc) and I'm trying to decide how best to organize this information in a database.
At its most basic everything within this particular umbrella is an event, so I thought of having a single Events table with fields for all the properties that might come up. This may be unwieldy, though, because the vast majority of the fields will end up being blank for many of the entires; but i'm not sure if that's actually a problem. The benefit of that way is that it will be easier to call and display all the events. But since many of the events will only have 'timestamp' in common, i question whether they belong on the same table.
I'm not sure that it makes sense to have a table for every kind of event, because taken separately most of the events have only one property other than timestamp, and they will often have to co-mingle. (many types of data will often but not always come in a group)
some types of events have durations. some are comparatively very rare. One class of events are normally a rate that stays the same unless the rate is altered for good or with a temporary override (those are the ones i'm most worried about). Some are simple binary tags (which i was planning on having a linking table for, but to make that easy I would need/prefer an overall event_id to link them with.
My inclination is that is best to have a few tables with closely related types of information rather than one table with everything and a whole lot of space.. but am not quite sure how to proceed.
I would love some advice on strategy for determining the best approach in a situation like this.
edit: Here is a rundown of the data types I'm dealing with in case it makes things more clear
events:
-blood glucose
timestamp
value
(tagged w/: from pump, manually entered
[pre-meal, post-meal (breakfast, lunch, dinner) before bed, fasting, hypo, high, hyper - which will be either manually entered or inferred based on settings or other user entries], before/after exercise etc i imagine would be better off dynamically generated with queries as necessary. though could apply same paradigm to the meals?
-sensor glucose (must be separate bc it is not as reliable so will be different number from regular bg test, also unlikely to be used by majority of users.)
timestamp
amount
-bolus
(timestamp)
bolus total
food total
correction total
active insulin**
bolus type - normal[vast majority] square wave or dual wave
-food
(timestamp)
carb amount
carb type (by weight or exchanges) <- this could probably be in user settings table
food-description
carb-estimated (binary)
meal? - or separate table.
(accompanying bolus id? though that seems to finicky)
-meals
timestamp
mealname (breakfast, lunch, supper) (or mealnames table? seems excessive?)
-basal
timestamp
rate per hour
rate changes throughout day on regular pattern, so either automatically fill in from 'last activated pattern' (in the form midnight: 0.7/hr, 7am: 0.9/hr, 12pm: 0.8/hr etc)
create new pattern whenever one is used
-temp basal
(regular basal pattern can be overridden with temporary basal)
temp basal start
?temp basal end and/or temp basal duration
temp basal amount
temp basal type -> either in % or specific rate.
-exercise
start-time
end-time
intensity
?description (unless 'notes' is universal for any event)
-pump rewind (every 3 days or so)
-time
-pump prime
-amount
-type (fixed or manual)
-pump suspended
start-time
end-time
-keytones
time
result
-starred
event
-flagged
event
-notes
timestamp
(user can place a note with any event to provide details or comments, but might want a note where there is no data as well.)
(i want a way for users to flag specific events to indicate they are result of error or otherwise suspect, and to star events as noteworthy either to discuss with doctor or to look at later)
**only place I get active insulin from is when a bolus is entered, but it could be useful other times as a constantly tracked variable, which could be calculated by looking at boluses delivered up to X time ago where X is the Active Insulin Time.
other infrequent events (likely 2-10 per year):
-HbA1C
time
value
-weight
time
value
units
-cholesterol
time
value
-blood pressure
time
value
-pump settings (will need to track settings changes, but should be able to do that with queries)
-timestamp
-bg-target
-active insulin time
-carb ratios (changes throughout day like basal)
-sensitivity
-active insulin time
concerns. 1) overarching 'events' table with a type, to quickly bring back all events in period of time without having to query every single table? (disadvantage is how do I work with events with duration? have optional end-time on event table?)
2) this is a local database which will generally be one user, and there will never be a need to compare or interact any of the records of other users if it is synced online, so i was thinking of just keeping one version of the database per user, though maybe adding a 'user' id as it is uploaded.
3) many of the events will often go together for ease of interpretation and analysis (blood sugar, meal, food, bolus, notes for instance), i gather it's better to do that after the fact with queries rather than hardcoding anything to maintain integrity.
Some info on what the database will be used for: -A visual representation of all data types over the course of a day -Average all test results and percent of insulin which is used for food, correction, basal. -As well as specific advanced queries like: list up to 20 examples of the difference in glucose level between before bed glucose and morning glucose when no food eaten and no exercise w/in 2 hours of bed, since settings were last changed, etc. -program will automatically assign tags based on parameters. like if >20 carbs are eaten during assigned 'lunch' period, it will say that food is lunch. if there are two food intakes within 30 minutes (or 'meal length' preference) it will group them as one meal.. not totally sure how that will function right now.
