I am creating an application that, by its main function, tracks various data over time (blood glucose levels, insulin doses, food intake, etc.), and I'm trying to decide how best to organize this information in a database.
At its most basic, everything in this particular umbrella is an event, so I thought about the fact that I have one event table with fields for all the properties that may occur. This can be cumbersome, though, since the vast majority of fields eventually become empty for many of them; but I'm not sure if this is really a problem. The advantage of this method is that it will be easier to trigger and display all events. But since many of the events will only have a timestamp, I doubt if they belong to the same table.
I'm not sure it makes sense to have a table for each kind of event, because, taken separately, most events have only one property other than timestamp, and they often have to mix. (many data types are often, but not always included in the group)
some types of events have a duration. some of them are relatively rare. One class of events, as a rule, is a bet that remains unchanged if the speed does not change forever or with a temporary redefinition (these are the ones I am most worried about). Some of them are simple binary tags (for which I planned to have a link table, but in order to make it easy, I will need / prefer a common event_id to associate them with.
My tendency is that it is best to have several tables with closely related types of information, and not with one table with all and a lot of space. But I'm not quite sure how to proceed.
I would like some advice on strategies to determine the best approach in such a situation.
edit: Here's a summary of the data types I'm dealing with, in case this makes things clearer.
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
Problems. 1) a comprehensive table of "events" with a type to quickly return all events over a specific period of time without having to query each table? (The disadvantage is how can I work with events with a duration? Is there extra time in the event table?)
2) this is a local database, which, as a rule, will be one user, and there will never be a need to compare or interact with any of the records of other users if it is synchronized online, so I thought about just saving one version of the database for each user, although it is possible to add a user ID when loading it.
3) many of the events are often combined for ease of interpretation and analysis (blood sugar, food, food, bolus, notes, for example), I'm going to do it better after the fact with queries, and not with hard coding to maintain integrity.
Some information about what the database will be used for: Visual representation of all data types during the day - Exclude all test results and the percentage of insulin that is used for nutrition, correction, basal. -As well as specific extended queries, such as: a list of up to 20 examples of the difference in glucose level between the dose of glucose and morning glucose, when there is no food and there are no exercises without 2 hours, since the settings were changed for the last time, etc. -program will automatically assign tags based on parameters. as if> 20 carbohydrates were eaten during the appointed “lunch”, he would say that the food is lunch. if there are two meals within 30 minutes (or the preference for "meal length"), they group them as one dish. Not completely sure how this will work right now.