I am asking for some design/architecture advice regarding building an app with potentially dynamic data.
We have a process where engineers carry out tasks, which they then log. In general, the data recorded for each task is the same, date, duration, engineerID, taskType etc.
A task can be in one or more categories, and if a certain category is selected there are 2-3 extra questions to answer.
The system is very basic and easy to design, until we conisder the category specific questions. We have been thinking of two approaches:
develop a dynamic system where questions are completely seperate from tasks and categories in the database. There is a mapping table to say which questions are asked for each task, and the web front end dynamically displays these questions based on their type which is stored as an attribute of the question (i.e. dropdown, check box, text box etc) This is much more difficult to develop but easier to administer when new quesitons need to be added.
We keep the system very simple, almost a star schema, where the fact table records the answer to each question. We have a column for each category specific question, but an answer is only recorded if that category is selected. This is simpler, but requires schema and development work if we need to add further questions.
Requirements gathering tells us that a category specific question can only be added after a hefty review and only once per year so the work is controlled.
given a small team of just me and a part time developer doing this in our spare time, I am veering to option 2.
am i missing any design options that may be better options?