3

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?

3
  • I think your logic for querying these data is not that complicated. With sql server, use row_number(), partition by Id, order by Scope in descending order. Pick the row number = 1. The query is simple and performs very well especially if these fields are indexed. sqlserver.info/syntax/row_number-how-to-use-it – JeffO Nov 29 '16 at 23:19
  • Thanks @JeffO, I have actually done just that but I was wondering if there was a better solution, something completely different, maybe some tech already out there or in sql server that I don't know about to mask records maybe? I was also considering 6th normal form, but re-combining the data back in a view would be difficult and maintaining the database also becomes more difficult. – Anupheaus Nov 29 '16 at 23:49
  • 1
    Based on your description of your view (if..then...if...then) it didn't seem like it. I don't think you can answer "better" out of context. No matter what you do, you'll give something up. – JeffO Nov 30 '16 at 21:26
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.