Best database model for a specific scenario
Question

Imagine the concept of an appointment to the doctor. (just creating a scenario to make it easier to explain)

The doctor will have a dropdown in which he can select different values (Is patient vaccinated, Date of last appointment, Medication Observations). Depending on the option he choses, an input with the desired input type will show. After selecting and completing the input, the 1st dropdown will appear again and he now has the other 2 options (we remove the one that was previously chosen). And we repeat this until the doctor doesn't want to provide any more information. My question is, regarding the database model, which way is the best to handle this scenario?

mvp_badge
MVP
Solution

Right, so it is more like a questionnaire then. In that case, you have a table with questions, and for each question an answer type, which is an Id to a static entity that holds all the possible types of answers. Then you can choose how to store the answers. You could have an Entity per answer type, so an Entity with booleans, one for dates, etc., or you could have a table that can store those as Text (so you store "True"/"False", "2021-04-23" etc. and convert back when you need the value), though if the free text needs a lot of space, you better create a seperate Entity for that, or you'll waste a lot of database space storing the simpler data types.

In case you also want to store multiple choice answers, you also need an Entity to hold the possible answers for a specific question. So you'd have a question Id, and the possible answers (and some Order Attribute for sorting). You can then store the answer as the answer Id. In case of more than one answer being possible, you could store a comma-seperated list, or have a seperate Entity to store the combination of Question + Answer Ids.

Basically, it all depends on how simple or complex you want to make things :).

mvp_badge
MVP

Your datamodel doesn't depend on the behaviour of your app (which is what you describe), but on the data you want to store. That's the #1 thing you need to think about.

The question still remains. I have a lot of options, but most of them might not be even used in a lot of situations. I'm unsure about the best aproach

mvp_badge
MVP

Well, do you want a generic approach, more akin to a questionaire builder or the like? Or is this a specific app for a specific group of people (like docters) with a finite set of questions?

mvp_badge
MVP

And the answers to those questions, are they always free text? Or are they indeed more a questionair kind of thing? Like multiple choice?

they can be bool, date, free text, anything can go in there

mvp_badge
MVP
Solution

Right, so it is more like a questionnaire then. In that case, you have a table with questions, and for each question an answer type, which is an Id to a static entity that holds all the possible types of answers. Then you can choose how to store the answers. You could have an Entity per answer type, so an Entity with booleans, one for dates, etc., or you could have a table that can store those as Text (so you store "True"/"False", "2021-04-23" etc. and convert back when you need the value), though if the free text needs a lot of space, you better create a seperate Entity for that, or you'll waste a lot of database space storing the simpler data types.

In case you also want to store multiple choice answers, you also need an Entity to hold the possible answers for a specific question. So you'd have a question Id, and the possible answers (and some Order Attribute for sorting). You can then store the answer as the answer Id. In case of more than one answer being possible, you could store a comma-seperated list, or have a seperate Entity to store the combination of Question + Answer Ids.

Basically, it all depends on how simple or complex you want to make things :).

mvp_badge
MVP

Hi Tony,

What I sometimes do is google a reference data model, just to validate if what I designed makes sense or if I might have forgotten something, or just for inspiration:

Regards,

Daniel


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.