I am looking for advice on database design for a web app I am building.
In the web app (Angular, REST API, Oracle SQL database), I am constructing a web form dynamically. I chose dynamic form construction, because I will need to display many similar, but different forms (around 30), and the questions asked on the form will be subject to change at the client’s request.
For the prompts on the form that are a drop-down, list-view, etc. prompt type, there will be a predetermined list of available responses (e.g. Yes, No; Product category: clothing, electronics, food, sports). I need a way to store these available response values in the SQL database. But here’s the catch: some of these lists of available response values must come from other tables in the database (e.g. product model), and some are static (e.g. Yes, No).
For the solution I am considering now, I am making a table to hold available responses — let’s call it
available_responses. There is another table
prompts holding the prompt metadata. In order to get the available response values from other tables, I will use “magic strings” to represent that the value should be replaced by a query. A pseudocode example:
TABLE prompts ( prompt_id (primary key), required Bool, control_type Int, -- e.g. textbox, dropdown, etc. set_id Int, -- corresponds to available_responses.set_id ... ) TABLE available_responses ( option_id (primary key), set_id Int, value String -- if surrounded by double underscore, consider it a "magic string" (e.g. __model__) )
With this, the REST API would get the list of available responses, and for each result that is a “magic string”, run a predefined query to get the actual set of values from the database, and return the final data structure to the front-end.
The biggest problem that comes to mind with this solution is that adding a new “magic string” to the table requires adding a new query method to the REST API.
available_resopnses.set_id exists because of the multiple response options to be associated with a prompt (e.g. Yes, No, NA would be set_id 1;
__model__ would be set_id 2). Would it be a better design to have a
available_response_sets table in order to define proper foreign key relationships?
TABLE available_response_sets ( set_id (primary key), option_id Int (primary key, foreign key available_responses) ) TABLE available_responses ( option_id (primary key), value String -- if surrounded by double underscore, consider it a "magic string" (e.g. __model__) )
Am I just looking for advice on designing this system, about the potential pitfalls of my tentative design, and about alternative designs.