Displaying various subsets of content stored in a database

Question

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.

Answer 1

Use database views. Define one view per context. Each view will return only the columns required in that context. Columns can be aliased to return names that are meaningful in that context.

If the application can determine which context number to use it can determine which view name to query.

Answer 2

Whether or not this is a good design is subjective, and depends on many factors. The first thing to determine is whether or not restricting what the users sees in the UI is a matter of application state or application behavior.

If end users or a subset of users can configure the display of these UI components, then "what is displayed" is application state. What they see is data that should be stored so that the UI can be recreated later. In this case "what they see" is not behavior, but settings that should be stored some place. The database is a good place for this, and having a table describing those settings is good design. I would lean towards your proposed design with a what_to_display kind of table.

When end users cannot configure the display, you need to figure out why information is restricted. This leads us to application behavior. The one thing I am sure of in this case, is that the database is not the place to specify this. Behavior usually means logic, code — i.e. "not database tables." Relational databases are great for storing data, but not as good at storing logic. Logic tends to be in application code. Where in your application code this logic lives depends on why the information is restricted and the costs in time, maintenance and security imposed by that layer of the application.

Sensitive information might be hidden from one kind of user, but displayed to another. Other reasons for hiding information are use case specific. Perhaps the data is not relevant to a particular use case, so it is not displayed to the user. Limited screen real estate in the UI can be another reason to restrict information, so it fits better on screen.

Since you are using a REST API, first implement these restrictions in the UI layer. Only if the UI code becomes messy and difficult to test, or after measuring a noticeable performance problem, would I move this filtering logic into the REST service. Even then, where this filtering logic goes is subjective. It might just be application code in the controller or view model. It might be beneficial to enforce this at the SQL query level, but I wouldn't do this unless I measured a performance problem. Start out with the easiest solution that is quickest to implement when restricting what the user sees is a matter of application behavior.