I am currently working on a project where I am recreating a MS SQL Server database in Azure SQL database. I have been given the current schema (although not with the actual column data type) and I came across this:
Question (QuestionID, QuestionDescription, UnitOfMeasure, SQLDataType)
QuestionAnswer (QuestionID, AnswerID, Answer, SQLDataType, UnitOfMeasure)
So, the database contains a variety of questions which each are answered/measured with different types of data (int, text, date, etc,..) and ALL answers are stored together with a column identifying the data type used in the "Answer" column.
I would like to know if this is a common practice and how this can be implemented? (Multiple types of data in one column). An alternative would obviously be to keep all questions together but seperate out the various answers into different tables, but I can see how that becomes cumbersome with 30+ different questions.
This is a pattern that we use in production for the same type of problem. Yes, it works - you just have to make sure that each Answer
can actually be cast to the given SQLDataType
. We do this at insert/update time.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With