Track health indicators

[andersmnystrom]

I am in the process of creating an app that will be a support for assistants to severely ill patients. The handling of medication, food, etc. One function should be the opportunity to track how the user is feeling and different health indicators. The idea is that one or more times per day you will enter how the person is feeling on a scale of 1-5 or 1-10. What one chooses to track should be able to vary from user to user. It can be things like sleep, stomach functions, epileptic seizures, etc. The idea is also that one should be able to follow the development of these indicators in some form of dashboard. How would you structure a function like this? I have come a bit on my way with other part of the app, but when it comes to this part, it stands completely still for some reason. English is not my first language so I hope it is possible to understand what I mean, if not, please ask!

[andersmnystrom]

I have worked some more on this and believe that the structure I have arrived at isn’t optimal. I'll try to describe the function I’m building a little more. The idea is that the patient should answer a number of questions about their health a number of times a day. How many times a day and the number of questions should be possible to vary.

 

An example: A patient should answer a couple of questions three times a day. Once in the morning, once in the middle of the day and once in the evening. Some questions will be used on each occasion, while some questions are used one or two times per day. Simplified examples of the questions are

1. How does the stomach feel (answer 1-10 where 1 = Very bad and 10 = Very good). This question is asked both morning, mid-day and evening

2. How have you slept this night? (answer 1-10 where 1 = Very bad and 10 = Very good). This question is asked every morning.

 

However, I am afraid that I have made the structure overly complicated. I attach a picture of the tables that are included in the function and how they are connected. The tables are as follows:

 

 

 

Questions: one record is a question. It also includes fields as “Type of question” etc.

 

Survey: my idea is that a survey is a collection of a number of questions. It contains fields as “Description” and “Type of survey”. A question should be able to belong to different surveys.

 

SurveyQuestions: acts as a join table between questions and a survey.

 

Answer: one record is an answer to a survey question

 

SurveyMoment: a record here represents the time that a survey and its questions is answered, i.e. when the answers are given.

 

Everything feels exaggerated as said. Is there anyone who has a suggestion for a better solution. Right now, I feel that the SurveyMoment-table is completely unnecessary. The original idea was to be able to tie all the answers to a survey to the same exact time, but it can be solved in other ways.

 

It feels like my head does not work at the moment so I appreciate all the help and tips you can give me!

[Ocean West]

it may seem over exaggerated to have data that is exploded out in to records with many join tables - this way you could with less effort aggregate data for reports and see trends and or pinpoint peaks or valleys in data set over time... 

the other option would be to store the response data and questions along with the survey as a JSON object where a 'survey' with all questions and answers are captured in one record - however this will be at the expense of being able to quickly analyze the data - unless you can push that structured JSON object to something like java script to unpack it so that you can visualize or search data sets.

https://www.geistinteractive.com/widget/pivot-table/

In your data example above i would suspect you will also need to have a Survey Template structure once a survey is published the questions and expectant results of questions (boolean, number, text, pick list, Likert Scale)  would need to be immutable so that participant 1 and participant 99 would have all have the exact same set of questions, as not to skew any results. There are several ways to seed the survey template to the tables,  when you begin the test, take the survey template and walk the hierarchy creating all the needed join records in all needed tables. Via relationships, matching all the primary to foreign keys. 

You could export import a template that has all the record structure pre built, in to the testing tables. You could at runtime explode out template survey using a virtual list where records are created on the fly for each question.

It gets more complex when your need to have branching logic in to your survey / template  such as when some questions can only have any weight or merit if the preceding question crossed a threshold that would require them to submit an answer.  As an example questions that would only be relevant to female patient. The other option is to include a Not Applicable to every question so that your aggregate data can be properly weighted to prevent bias or skew.

 

[OlgerDiekstra]

Your diagram looks like an inverted anchor-buoy. For readability I'd flip it. The Survey TO is the anchor, all other TO's hang off it.

I agree that the SurveyMoment is unnecessary based on the info provided. The Response table would have a Created (date) and CreatedBy fields (ideally) and an Answered (date) and AnsweredBy field (along with a Modified (Date) and ModifiedBy field). That automatically gives you the info the SurveyMoment would store.

Each Response record would also need to link to a Question (therefore store the id of the question) as that is what it relates to. It doesn't really relate to the survey so much. Looking at the Survey and the Responses tells you nothing, you have to look at the Survey and Questions to make sense of the Responses.

The only purpose a SurveyMoment table could serve is if you want to record random and changing information about the time the survey is taken. Ie, sometimes recording time of day might be useful, other times whether the survey was taken right after a meal. Or something like else.

[andersmnystrom]

Thank you for your answers. Some of the writing goes over my head, but I try to take in as much as I can. Perhaps I should mention that this is a "hobby project" where I try to make this app for a friend whose child is multi severely multi-disabled. Hopefully it will support them when they take care for her.

I feel that the most important thing regarding this function is to get as much useful data as possible that then can be aggregated, analysed and followed up in different ways. That is, like you first mention in the top of your answer. Do I have a good basic structure for it now or is it something that should be changed or added?

I'm a little unsure what you mean by "Survey Template Structure". Currently, one answer the questions on a layout based on the SurveyMoment table. It is controlled by a script that has Survey_ID as a parameter. The script then creates new response in a loop where the responses gets connected  to the right survey questions. 

[OlgerDiekstra]

If this is a hobby project, why not post your file and let people here tweak it a bit? It's much easier to see what you're trying to do when the actual database is available.

[andersmnystrom]

Since the file contains quite a lot of sensitive information about a person, I do not want to post it all. But I removed everything except the survey-function and attached it here. Since this part is completely under development, it does not contain any actual data. The questions are, for example, only random simplifications etc. When answering the questions in the original file, you press a button bar with three buttons in it (Morg = morning, Midd = mid-day, Evening = evening). The button bar is there placed in a type of calendar layout, but here I have moved it to the layout “Create / administer Surveys”. The buttons are formatted in a way that should show if you answered one of the surveys or not but I’m unsure if it works now that I moved them.

 

The language of the file is really Swedish but I have tried to translate what is important to English.

Survey_part.fmp12

[Fitch]

Haven't looked at your file yet. But the type of structure you described above is typical of a system that is used for building surveys. I.e., it's made to be flexible so that questions can be added on the fly without adding new fields. I have built such systems. But is that really what's needed here? I'd consider a drastically simpler model where you simply add questions as fields to the Survey table and get rid of all those other tables.

[LaRetta]

On 6/11/2019 at 3:59 PM, andersmnystrom said:

I am in the process of creating an app that will be a support for assistants to severely ill patients. The handling of medication, food, etc. One function should be the opportunity to track how the user is feeling and different health indicators. 

I would suggest keeping the multi-table structure.  

As suggested by OlgerDiekstra, there is no need for the SurveyMoment table otherwise your structure looks correct; this is the standard structure for a survey-type solution.

On 7/6/2019 at 10:37 AM, andersmnystrom said:

Right now, I feel that the SurveyMoment-table is completely unnecessary. The original idea was to be able to tie all the answers to a survey to the same exact time, but it can be solved in other ways.

With the right structure, you can tie all the answers to a survey to the exact same time.  I suggest that you not change your structure to attempt to solve a display issue; rather, the other way around.  With the proper structure (as you have it), display is simpler.  BTW, I suggest you add a Patient table as well.

You have come to the right forum to seek help in working through your needs.  I'll also review your file tonight and provide additional input.  

[andersmnystrom]

15 hours ago, LaRetta said:

I would suggest keeping the multi-table structure.  

As suggested by OlgerDiekstra, there is no need for the SurveyMoment table otherwise your structure looks correct; this is the standard structure for a survey-type solution.

With the right structure, you can tie all the answers to a survey to the exact same time.  I suggest that you not change your structure to attempt to solve a display issue; rather, the other way around.  With the proper structure (as you have it), display is simpler.  BTW, I suggest you add a Patient table as well.

You have come to the right forum to seek help in working through your needs.  I'll also review your file tonight and provide additional input.  

Thank you! I will keep this structure the with your suggested changes. 

Im very greatfull for all the input you can give me! 

 

[comment]

If a person is taking the same survey several times, and you want to aggregate their responses to each instance, then the table you called SurveyMoment does make sense. Especially if they can take the same survey several times a day. 

A lot here depends on what do you actually plan to do with all this data, once you have collected it.  Of that you have told us nothing. If you hope to do any kind of meaningful analysis, then reject Fitch's suggestion of "a drastically simpler model" and stick to your original structure.

[Fitch]

Including multiple data points in a record does not preclude meaningful analysis. Just sayin'.

[comment]

19 minutes ago, Fitch said:

Including multiple data points in a record does not preclude meaningful analysis.

I am very surprised to hear you think so. I of course disagree emphatically. Certainly, eventually any analysis will be possible with enough effort put into it. But at what cost? 

Put another way: when did we start recommending denormalization as default?

[andersmnystrom]

22 hours ago, comment said:

 A lot here depends on what do you actually plan to do with all this data, once you have collected it.  Of that you have told us nothing. If you hope to do any kind of meaningful analysis, then reject Fitch's suggestion of "a drastically simpler model" and stick to your original structure.

I'm not entirely clear on how the information will be used, but I imagine that you should be able to follow the selected answers on a line chart over time together with a trend line, perhaps on a dashboard. 

Another part of the app will track all medicine and food that patients gets. It would be interesting if you could see any changes in the perceived health/mood adjacent to changes of the medication or diet. Or conversely, if you see shifts in the patient's perceived health/mood, one could examine if, before that, any changes were made to the medication, diet or otherwise.

[Fitch]

On 7/9/2019 at 11:15 AM, comment said:

I am very surprised to hear you think so. I of course disagree emphatically. Certainly, eventually any analysis will be possible with enough effort put into it. But at what cost? 

Put another way: when did we start recommending denormalization as default?

All I suggested was that you could have multiple fields in a table. Attributes of an entity.

Like I said earlier, I've built questionnaire/survey systems. Sometimes it makes sense to store the answers as individual records. But then to analyze the data I would say does require a fair amount of effort, e.g. to build a pivot table.

Other times it makes sense to store the answers as fields. E.g., we use a Case Log database in my office (public health) to gather data for certain types of outbreaks. Each symptom (cough, headache, fever) is a checkbox on a case record. From this we can produce epidemic curves and other analyses, with relatively little effort.

[comment]

A simple example: 20 questions, all requiring a numeric answer. In order to show the average response to each question, you would need to add 20 summary fields. If you also want to see the minimum and the maximum, the number jumps to 60.

A slightly more complicated scenario: you want to exclude outliers when producing the averages. You will have to create a succession of 20 different found sets, save the average of each and then solve the problem of how to display them.

These are just two examples of what I mean by "meaningful analysis". 

[Fitch]

That's a good example. The number of questions and the reporting requirements of course would factor in to which approach to take. How would you display the min/max/averages in your scenario?

[comment]

14 hours ago, Fitch said:

How would you display the min/max/averages in your scenario?

Since each response would be a record, it would be a simple matter of sorting by question and showing the average in a sub-summary part, using a single summary field.

[andersmnystrom]

On 7/8/2019 at 8:48 PM, LaRetta said:

I would suggest keeping the multi-table structure.  

As suggested by OlgerDiekstra, there is no need for the SurveyMoment table otherwise your structure looks correct; this is the standard structure for a survey-type solution.

With the right structure, you can tie all the answers to a survey to the exact same time.  I suggest that you not change your structure to attempt to solve a display issue; rather, the other way around.  With the proper structure (as you have it), display is simpler.  BTW, I suggest you add a Patient table as well.

You have come to the right forum to seek help in working through your needs.  I'll also review your file tonight and provide additional input.  

Not that I have any right to expect an answer here but I was still a bit curious if you had time to check the file and if you had any input?

[LaRetta]

I've been unable to answer or check your file until now but it looks good.  You have an appropriate and logical structure (considering everything that has been discussed and from what I can tell in the file itself).  How are you doing on it - any additional questions?  I believe that Michael (Comment) covered everything you've asked so far, right?  Have you generated any reports yet?🙂

[andersmnystrom]

Thanks for your reply! I think I have arranged how questions are created and answered so now I concentrate on how the answers should be aggregated, presented and analysed.  I am reading about and trying to learn SQL, the web-viewer and java-charting as I find the built-in chart-function to be somewhat restrictive.