I am looking for advice on database design for a web app I am building.
Part of the website needs to display content from a SQL database dynamically depending on the component's context. To be more specific, I need the component to display a subset of columns from the item_info
table, depending on the value in another table item_context
.
I'm using Angular to build the site, and accessing the database via a REST API.
I have an idea for how to do this, but I am wondering if there is a better way.
The design I am considering is to have a table item_info
that contains all the item properties, and another table that describes the display context item_context
. Then use a third table what_to_display
to describe which columns of item_info
should be displayed. There are sufficiently large number of rows in item_context that it would not be practical to enumerate them in the application itself.
Here is an pseudocode example of the database tables:
TABLE item_info (
item_id (primary key),
prop_1,
...
prop_n
)
TABLE item_context (
context_id (primary key),
what_id (foreign key what_to_display),
...
)
TABLE what_to_display (
what_id (primary key),
prop_1 Bool, -- display if true
...
prop_n Bool
)
With this, the REST API would discard columns from the results of querying item_info
, based on the results from querying what_to_display
for a given item_context
.
I don't really like that what_to_display
has to have the same column names as item_info
, which creates a hard-coupling in the database schema. But I can't think of a way around that.
Is this a good design? Do you have an alternative design to suggest?
Edit
What is displayed in the component for each context is not configurable by the user, it is predefined by the admin.