6

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?

5
  • 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, 2016 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, 2016 at 23:49
  • 4
    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, 2016 at 21:26
  • Why do you need an Id column when all records in your database will have the value 1? Or do you plan to support multiple apps with the very same configuration table, each one getting a different Id?
    – Doc Brown
    Jan 23 at 15:36
  • Not totally the same but relates strongly to your data structure: softwareengineering.stackexchange.com/a/446760/46605 when you also want rules like: Do not allow departments to override security setting X but do allow companies to do so. Sep 20 at 17:17

1 Answer 1

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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