Hello all,
I have a database that stores tests for students at a 2nd language school. I designed it such that there is a parent Question table that can relate to any ONE of a few QuestionType tables--Vocabulary, Writing, Speaking, etc--in a one to one relationship. I'd like to add a column 'type' to the parent Question table that contains a string of the type of question it is related to where the string is take from a fixed list of possible strings--like an Enum type. Furthermore, having this type column automatically populated at the time it's primary key is added to a child table as a foreign key and based on which child table that is, would be fantastic. My goal is to make it easier for developers to get the type of question, rather than having to query all possibly related child tables (only one of them will be related) , so maybe this isn't the best solution since it sort of adds redundancy. I'm open to other options/advice. Note: the query will be starting from the question (parent table) rather than the child necessarily so, otherwise it would be solved.
Thanks!
Jon