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
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
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?
What is displayed in the component for each context is not configurable by the user, it is predefined by the admin.