Should 'system data' be in a database?

Question

I have designed a database for an application which contains user settings. This database will come preloaded with several system user settings. These may change from version to version. How should I deal with updating these settings?

The solutions I came up with:

1. Apply a boolean 'system' field to the settings tables and replace all system settings when the version number of the database changes. (which is very annoying while designing these settings as you have to constantly wipe the database while developing)
2. Don't put system settings in the database and somehow merge the system data with data from the database when querying them.

Currently I'm doing the first, but somehow the 2nd seems more appropriate. What should I do and how can I seamlessly union external data with data from a database when using the Microsoft Entity Framework?

Answer 1

Since there is a large number of operations you may end up wanting to perform on the data beyond a simple union (inner and outer joins to see who matches system settings or doesn't spring to mind) I'd suggest putting the system data in the same structure as the user data.

This also allows behaviours like filling in defaults from system data when user's don't have a particular value.

There are some interesting points about managing settings and overrides in this blog post if you've got the time to read something quite that long.

Answer 2

I would load the default value for the user first, then load the user customised values over the top. That way you could still change the defaults, but if the user has change a particular setting they won't lose it.

Answer 3

I'm not sure what you want to achieve with 1), but I can answer 2).

All generated entity classes are partial classes, so you can extend these with your own properties and method. So you could add an extra property to an entity which fetches its data from an external source (eg a configuration file).

Now, this may not be the best solution since there is no way to fetch all the information you need at once (when retrieving a large number of entities, for example).

Answer 4

I'm not sure if this is really getting at your question, but I thought it was an interesting little database exercise. Suppose you have a table called "setting" as you propose in #1:

+------------+--------------+-----------+---------------+
| setting_id | setting_name | is_system | setting_value |
+------------+--------------+-----------+---------------+
|          3 | foo          |         1 | Blue          |
|          4 | foo          |         0 | Red           |
|          5 | bar          |         1 | Green         |
|          6 | baz          |         1 | Yellow        |
|          7 | baz          |         0 | Orange        |
|          8 | quux         |         0 | Purple        |
+------------+--------------+-----------+---------------+

The primary key would be on setting_id and a unique constraint is applied to the columns setting_name and is_system (which is Boolean).

To find all of the settings that are currently active (assuming user settings override system settings), the following query will do the job:

SELECT  setting_name, setting_value
FROM    setting s
WHERE   is_system = (
                SELECT  MIN(is_system)
                FROM    setting si
                WHERE   s.setting_name = si.setting_name
        );

Running the query produces this result:

+--------------+---------------+
| setting_name | setting_value |
+--------------+---------------+
| foo          | Red           |
| bar          | Green         |
| baz          | Orange        |
| quux         | Purple        |
+--------------+---------------+

So, if a user setting exists it is fetched, otherwise the system default is used instead.

Using the MIN() function in this way is kind of a perverse way to operate on Boolean values, but it's simple enough.