We receive patient data that includes case level info (hospital id, patient id, length of stay, surgery date) as well as surveys/educational material that the patient completed as well as their responses to the surveys. We are developing a star schema data model to store this data. Below is an example of raw data that we receive in the structure we receive it: In summary there are three tables. The case data table that store high level case data, the content data that contains the different material that the patient completed. Within this table there are scores for the surveys only. And lastly the survey responses table that contains the question and answer from the survey.
There are two main use cases for this data. First is aggregating
app_logins to find averages, min, max, etc. Second, is aggregating
scores in the content data to compare preop and postop scores.
content responses table is used to generate the
scores, and some users like to see if there are patterns in responses, such as understanding whether 'Is level of pain consistent with low or high survey scores?'
content responses only contains data for surveys, not the educational material found in the
Some of the concerns is that:
- we have a factless fact (
FactSurveyResponses) that is mainly used for displaying questions/answers for a given patient survey. This is probably OK overall but begs the question is there a better way to store this information?
- Some columns, like surgery_date, are used as filters on a Power BI dashboard, so they should be available on each fact to properly filter out the data; however, this seems like it would bloat the facts on the database side because it is unlikely that someone would want to see how a patient responded on a certain date (at least no one has asked for this before).
I am hoping to get some feedback on whether this is a suitable model, or if there are any adjustments or other considerations someone would make. I attached an entity relationship diagram of the model we anticipate to build.