I'm setting up a database to handle a FAQ page for different musical venues. The basic structure is:
id | venue_id | category | question | answer
category links to an enum table so our app can allow users to filter questions. I am trying to decide if it makes sense to use an enum table for questions since all venues will be able to answer the same (optional) questions.
Our current design shows there could be around 200 total questions. Obviously this isn't enough to cause any type of functional issue, but I am wondering if it is logical to have an enum table this large. What are the things that I should consider when making this decision?
Additionally, if questions are tied to a category would it make sense to eliminate the category all together in lieu of the question enum or is better not to bog a enum table down with more columns than neccessary?
Best Answer
I recommend that you keep the questions in a separate table and have a FK in the answer/response table. I like my enum types to represent a well-defined and discrete concept. For example, in a order tracking system, the status of an order would be a good enum. A concept like a question does not fit that requirement. A question can have attributes (e.g is the question mandatory or optional, are the responses constrained or open-ended, etc), which in my opinion makes it something I would want to store in a table.
I can imagine a question fitting multiple categories. If that is the case, then there should be a category table and a link table between the question table and the category table. If a question can fit only one category, then that would be a good fit for an enum column in the question table.