-2

I have to create an SQLite DB that models a survey with some ordered content; this content can be a question, an image or a simple text field (just like Google Forms). Each content doesn't have anything to do with the other, except questions which can have a list of attached images to them.

What would be the best way to model this situation? I thought about creating a "Survey" table and a "Content" table that has only an integer ID, and that same ID is then "duplicated" into each table ("Question", "Image" or "TextField"), but then I think I would have to insert both values for the Content and values for a specific content (Question, Image or TextField) every time I need to insert a new content. I don't think it would be a big problem, but if there is an way to model this better, I would like some advice.

Fsanna
  • 347
  • 1
  • 4
  • 11
  • 1
    Does this answer your question? https://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database – Neville Kuyt Feb 22 '21 at 11:09
  • @NevilleKuyt yes it helps a lot, now I know that what I was thinking was one of the right ways to do it. Do you think it would be a problem having a table with a single column (the ID)? – Fsanna Feb 22 '21 at 11:17

1 Answers1

1

Your approach is an example of 'table per type' as defined in this answer.

Conceptually, you're saying "there are 3 kinds of content, and the one thing they share is their relationship with a survey, as captured in the content table". You might include in that table an explicit type indicator along the ID - this will make your code a little more explicit. You may also find you need to capture meta data like "status", "date_entered" etc. which is common across subtypes.

By including a type indicator column, you make it easy to find out what the type of a content item is. So, if you want to show the summary of a question, you could do something like

select content_type, count(*)
from content
where question_id = ?
group by content_type

to show the number and type of responses.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • I left out on purpose metadata and optional values just to make the question simpler to read. Will this type indicator be of any help on creating queries without resorting to use external functions (with any programming language)? – Fsanna Feb 22 '21 at 14:07