Algebraic data types in SQL

I'm working on an application that has a survey-like structure. As such, the there are different types of questions that need to be stored in the database e.g. multiple-choice, true/false, essay etc.

For this reason, it's a bit difficult to structure the SQL for the questions and answers tables. This sort of structure is considerably easier in MongoDB, because of the existence of discriminator fields.

Has anyone implemented something similar in SQL before?

Comments

[John Wright]

As a newbie, I am proud I understand the question after reading mongoosejs.com/docs/discriminators.... I mean SQL is super flexible, you just make it say whatever you need it to say. E.g. I can imagine a table called question and survey, you can guess how those 2 are related. Then you could have an every slightly so generic table called answer (in general you don't want to be generic in SQL so don't go overboard here, just be pragmatic). On answer you could have multiple fields for different types of answers, such as text_essay which could be a text type and yes_no which could be a boolean type, and choice which could be a simple integer which references options through a multiple_choice table. You can make everything link up super flexibly by giving everything an id and using simple foreign key references and then just test out and write your SQL in your favorite client, it's fun! Then use a simple driver to convert the sql types to code types and then perhaps do some simple algebraic choices in your client. There is one language to return the information and you can query it and change it indefinitely. I am new to all of this so might be over simplifying but I am pretty confident after years of trying to be more than a newbie, that you just shouldn't use an ORM and just work and work the SQL and you will be fine. SQL is built to be generic itself, so you can just declare exactly what you want. Don't try to build super abstract things on top of it, just build the things you need.

[Oluwakorede Fashokun]

Thanks for the suggestion. I hadn't considered just having nullable field on the answer table representing the question variants. It does make sense. I'll also check if there are constraints that prevent more than one of those columns being non-null.

[Brandin Chiu]

Which portions of the day are you looking to store?

Are you storing the answers themselves, or the structures of the questions?

If you don't need or plan on doing any kind of complex queries using the data structure, and you just need a data store, you can make use of something like MySQLs JSON data type and just store the entire thing like a document.

Otherwise a relational system should have no problem breaking up your questions.

Simply store the question type and possible values in separate tables.

[Oluwakorede Fashokun]

In the case of multiple-choice questions, I plan to store the questions, choices and answers. In every other case, just questions and answers. Since I'm using Hasura, it really has to be type-safe, to make sure there are no surprises or edge cases when parsing.

[David]

This is an interesting question. I don't know the answer, but I am curious. I think this question is worth asking on Hacker News (Ask HN). If you get answers, I'd love to see this post updated with them or linked to the thread :D