How to set up a sql database to cater for user records, group records and default records?

Question

Outline

I have an application that loads data from a database. I'm not talking about client data here though, I'm talking about application configuration. The database will therefore come with some default setup, which I want to remain unchanged so that future updates can just be imported and overwrite that setup.

I would like for the client to be able to overwrite certain parts of that setup and/or add to it and/or remove from it (which probably won't actually be removed, just marked as deleted/disable). But I'd like a department and a user to also be able to do this and I'd also like for the most relevant changes to be loaded from the database.

Essential Requirements

1. The database contains standard application setup that cannot (or will not) be changed.
2. The database stores changes based on company, department or user that provide modifications to the standard setup. These might be added configuration, or modifications to existing configuration or the disabling of standard configuration.

My best effort

A table with a combined primary key from two columns: [Id] and [Scope]. [Scope] determines whether or not it is the default setup, a company record, a department code or a user code. The [Id] can therefore be the same for each allowing each level of the client to have their own configuration of that same record. For example:

Id | Scope | Name         | Other columns...
==============================
 1 |    -1 | MyApp        | ...             <- Default record
 1 |     0 | Company Name | ...             <- Company record
 1 |     1 | Department X | ...             <- Department record
 1 |   101 | User X       | ...             <- User record

I really don't like this approach, I have to create a view that groups all the same Ids and then picks the most relevant record based on the type (i.e. if there is no user record, then use the department record, if no department then use the company and if no company then use the default).

A lot of the information doesn't really change within the record either, maybe one or two columns (sometimes more though) and it seems wasteful to store the same data over and over again.

Request

I'm really looking for ideas from people about how better to store and organise this kind of setup please?

Answer 1

I think what you want is a polymorphic database table:

configurations

id | configurable_type | configurable_id | ...
==============================================
 1 | application       |            null | ...
 2 | company           |    [company_id] | ...
 3 | department        | [department_id] | ...
 4 | department        | [department_id] | ...
 5 | user              |       [user_id] | ...
 6 | user              |       [user_id] | ...
 7 | user              |       [user_id] | ...
 8 | user              |       [user_id] | ...

In Rails, I would make 4 queries to find the different configs and convert them to hashes:

application_config = Configuration
  .where(configurable_type: 'application')
  .first&.to_h

company_config = Configuration
  .where(configurable_type: 'company', configurable_id: company_id)
  .first&.to_h

department_config = Configuration
  .where(configurable_type: 'department', configurable_id: department_id)
  .first&.to_h

user_config = Configuration
  .where(configurable_type: 'user', configurable_id: user_id)
  .first&.to_h

Once you have hashes, you can merge them on top of each other to produce the final configuration:

configuration = application_config
  .merge(company_config)
  .merge(department_config)
  .merge(user_config)

One thing that may help with this would be to use a JSONB field to store the actual configurations (as opposed to putting the values in specific database columns). The two advantages this provides is that JSON converts to hashes well, and JSON is extensible, in case you come up with a new configuration option later.

Final thought: I would probably not store application default configuration in a database table. I would likely store it in YAML and read it once at load time (which I suppose you could do from the database, as well).