Should "system data" be in the database?

Question

I developed a database for an application that contains user settings. This database will be preinstalled with several user system settings. They may vary from version to version. How should I update these settings?

The solutions I came across:

• Apply the "system" logical field to the settings tables and replace all system settings when changing the database version number. (which is very annoying when developing these settings, because you have to constantly erase the database during development).
• Do not put the system settings in the database and somehow combine the system data with the data from the database when requested.

I am currently doing the first, but for some reason the second seems more appropriate. What to do and how can I combine external data with data from a database when using the Microsoft Entity Framework?

Answer 1

Since there are a large number of operations that you might want to perform on data outside of a simple join (internal and external connections, to see who matches the system settings or does not apply to spring), I would suggest placing the system data in the same structure, as user data.

It also allows behavior similar to populating default values ​​from system data when the user does not have a specific value.

, , - .

Answer 2

, . , , , .

Answer 3

, 1), 2).

, . , , (, ).

, ( , ).

Answer 4

, , , . , "", # 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        |
+------------+--------------+-----------+---------------+

setting_id, setting_name is_system ( ).

( , ), :

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

:

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

So, if the user parameter exists, it is selected, otherwise the system default is used.

Using a function MIN()in this way is a kind of vicious way to work with Boolean values, but it is quite simple.