0

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?

2 Answers 2

2

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.

1
  • I agree that generally enums are smaller, but I've had a lookup table before that listed EVERY SINGLE country and its states\provinces in the world, and all the code references the Country_ID and District_ID fields. This was well over 10K rows, and things ran fine with it. Nov 30, 2020 at 14:59
0

the answer is yes

mysql can stored max 65000 values(in reality so 3k) and postgres says it can store 63 bytes.

But your 2k would fit, but as questionnaires come, the structure looks not good.

Besides select a rdms and go for it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.