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)
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.