Tim Petri Tim Petri - 1 month ago 5
SQL Question

Storing different datatypes in one column to reduce table count

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.

Answer

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.