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
- The database contains standard application setup that cannot (or will not) be changed.
- 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?