Дизайн базы данных для пользовательских настроек

Ответ 1

В других ответах были изложены плюсы и минусы различных вариантов.

Я считаю, что ваш вариант 1 (мешок для имущества) является лучшим общим дизайном для большинства приложений, особенно если вы создадите некоторые меры защиты от слабых сторон мешков процеп.

См. следующий ERD:

В приведенной выше ERD таблица USER_SETTING очень похожа на OP. Разница в том, что вместо столбцов varchar Code и Value эта конструкция имеет FK в таблице SETTING, которая определяет допустимые параметры (коды) и два взаимоисключающих столбца для значения. Один из вариантов - это поле varchar, которое может принимать любой пользовательский ввод, другое - FK в таблицу с допустимыми значениями.

В таблице SETTING также есть флаг, указывающий, должны ли пользовательские настройки определяться FK или неограниченным вводом varchar. Вы также можете добавить data_type в SETTING, чтобы сообщить системе, как кодировать и интерпретировать USER_SETTING.unconstrained_value. Если вам нравится, вы также можете добавить таблицу SETTING_GROUP, чтобы помочь организовать различные настройки для обслуживания пользователей.

Эта конструкция позволяет вам управлять таблицами по всем параметрам. Это удобно, гибко и легко поддерживать, избегая при этом свободного доступа.

РЕДАКТИРОВАТЬ: Еще несколько деталей, включая некоторые примеры...

Обратите внимание, что приведенная выше ERD была дополнена дополнительными сведениями о столбцах (значения диапазона в SETTING и столбцы на ALLOWED_SETTING_VALUE).

Вот несколько примеров записей для иллюстрации.

SETTING:
+----+------------------+-------------+--------------+-----------+-----------+
| id | description      | constrained | data_type    | min_value | max_value |
+----+------------------+-------------+--------------+-----------+-----------+
| 10 | Favourite Colour | true        | alphanumeric | {null}    | {null}    |
| 11 | Item Max Limit   | false       | integer      | 0         | 9001      |
| 12 | Item Min Limit   | false       | integer      | 0         | 9000      |
+----+------------------+-------------+--------------+-----------+-----------+

ALLOWED_SETTING_VALUE:
+-----+------------+--------------+-----------+
| id  | setting_id | item_value   | caption   |
+-----+------------+--------------+-----------+
| 123 | 10         | #0000FF      | Blue      |
| 124 | 10         | #FFFF00      | Yellow    |
| 125 | 10         | #FF00FF      | Pink      |
+-----+------------+--------------+-----------+

USER_SETTING:
+------+---------+------------+--------------------------+---------------------+
| id   | user_id | setting_id | allowed_setting_value_id | unconstrained_value |
+------+---------+------------+--------------------------+---------------------+
| 5678 | 234     | 10         | 124                      | {null}              |
| 7890 | 234     | 11         | {null}                   | 100                 |
| 8901 | 234     | 12         | {null}                   | 1                   |
+------+---------+------------+--------------------------+---------------------+

Из этих таблиц видно, что некоторые пользовательские настройки, которые могут быть определены, - это "Любимый цвет", "Максимальный размер позиции" и "Минимальный лимит предмета". "Любимый цвет" - это список буквенных букв. Пункты min и max - это число с допустимыми значениями диапазона. Столбец SETTING.constrained определяет, выбирают ли пользователи из связанного ALLOWED_SETTING_VALUE или им нужно ввести USER_SETTING.unconstrained_value. Графический интерфейс, который позволяет пользователям работать со своими настройками, должен понять, какой вариант предложить и как обеспечить соблюдение ограничений SETTING.data_type и min_value и max_value, если они существуют.

Используя эту конструкцию, вы можете приводить в таблицу допустимые параметры, включая достаточное количество метаданных, чтобы обеспечить соблюдение некоторых рудиментарных ограничений/проверок на значения, выбранные (или введенные) пользователями.

EDIT: Пример запроса

Вот пример SQL, используя приведенные выше данные, чтобы отобразить значения параметров для данного идентификатора пользователя:

-- DDL and sample data population...
CREATE TABLE SETTING
    (`id` int, `description` varchar(16)
     , `constrained` varchar(5), `data_type` varchar(12)
     , `min_value` varchar(6) NULL , `max_value` varchar(6) NULL)
;

INSERT INTO SETTING
    (`id`, `description`, `constrained`, `data_type`, `min_value`, `max_value`)
VALUES
    (10, 'Favourite Colour', 'true', 'alphanumeric', NULL, NULL),
    (11, 'Item Max Limit', 'false', 'integer', '0', '9001'),
    (12, 'Item Min Limit', 'false', 'integer', '0', '9000')
;

CREATE TABLE ALLOWED_SETTING_VALUE
    (`id` int, `setting_id` int, `item_value` varchar(7)
     , `caption` varchar(6))
;

INSERT INTO ALLOWED_SETTING_VALUE
    (`id`, `setting_id`, `item_value`, `caption`)
VALUES
    (123, 10, '#0000FF', 'Blue'),
    (124, 10, '#FFFF00', 'Yellow'),
    (125, 10, '#FF00FF', 'Pink')
;

CREATE TABLE USER_SETTING
    (`id` int, `user_id` int, `setting_id` int
     , `allowed_setting_value_id` varchar(6) NULL
     , `unconstrained_value` varchar(6) NULL)
;

INSERT INTO USER_SETTING
    (`id`, `user_id`, `setting_id`, `allowed_setting_value_id`, `unconstrained_value`)
VALUES
    (5678, 234, 10, '124', NULL),
    (7890, 234, 11, NULL, '100'),
    (8901, 234, 12, NULL, '1')
;

И теперь DML выберет пользовательские настройки:

-- Show settings for a given user
select
  US.user_id 
, S1.description 
, S1.data_type 
, case when S1.constrained = 'true'
  then AV.item_value
  else US.unconstrained_value
  end value
, AV.caption
from USER_SETTING US
  inner join SETTING S1
    on US.setting_id = S1.id 
  left outer join ALLOWED_SETTING_VALUE AV
    on US.allowed_setting_value_id = AV.id
where US.user_id = 234

См. это в SQL Fiddle.

Ответ 2

Вариант 1 (как уже отмечалось, "сумка свойств" ) прост в реализации - очень мало анализа спереди. Но у него есть множество недостатков.

• Если вы хотите ограничить допустимые значения для UserSettings.Code, вам нужна вспомогательная таблица для списка допустимых тегов. Таким образом, у вас есть (a) отсутствие проверки на UserSettings.Code - ваш код приложения может выгружать любое значение в, упускать шанс поймать ошибки или вам нужно добавить обслуживание в новый список допустимых тегов.

• UserSettings.Value, вероятно, имеет строковый тип данных, чтобы вместить все различные значения, которые могут в него входить. Таким образом, вы потеряли истинный тип данных - integer, Boolean, float и т.д. И проверку типов данных, которые будут выполняться RDMBS при вставке неправильных значений. Опять же, вы купили себе потенциальную проблему с QA. Даже для строковых значений вы потеряли возможность ограничить длину столбца.

• Вы не можете определить значение DEFAULT в столбце на основе кода. Поэтому, если вы хотите, чтобы EmailLimitMax по умолчанию равнялся 5, вы не можете это сделать.

• Аналогично, вы не можете установить ограничение CHECK в столбце "Значения", чтобы предотвратить недопустимые значения.

• Подход пакета свойств не позволяет проверить код SQL. В подходе с именем column запрос, в котором говорится: "Выберите Blah из UserSettings, где UserID = x" получит ошибку SQL, если Blah не существует. Если SELECT находится в хранимой процедуре или представлении, вы получите сообщение об ошибке при применении метода proc/view перед тем, как код переходит к производству. В подходе к сумке свойств вы просто получаете NULL. Таким образом, вы потеряли еще одну функцию автоматического QA, предоставляемую базой данных, и внесли возможный необнаруженный баг.

• Как уже отмечалось, запрос на поиск UserID, где условия применяются к нескольким тегам, становится сложнее писать - для каждого тестируемого условия требуется одно соединение.

• К сожалению, Property Bag - это приглашение для разработчиков приложений просто вставить новый код в сумку свойств без анализа того, как он будет использоваться в остальной части приложения. Для большого приложения это становится источником "скрытых" свойств, поскольку они формально не моделируются. Подобно тому, как делать свою объектную модель с чистым значением тега вместо названных атрибутов: он предоставляет управляющий клапан, но вы не получаете всю помощь, которую компилятор предоставит вам по строго типизированным атрибутам с именем. Или как делать производственный XML без проверки схемы.

• Подход с именем столбца является самодокументированным. Список столбцов в таблице сообщает любому разработчику, какие существуют возможные пользовательские настройки.

Я использовал мешки для имущества; но только в качестве выходного клапана, и я часто сожалел об этом. Я никогда не говорил: "Господи, я бы хотел, чтобы явная колонка была имущественной сумкой".

Ответ 3

Каждый вариант имеет свое место, и выбор зависит от конкретной ситуации. Я сравниваю плюсы и минусы для каждого варианта ниже:

Вариант 1: Плюсы:

• Может обрабатывать множество опций
• Новые опции могут быть легко добавлены
• Общий интерфейс может быть разработан для управления параметрами

Вариант 1: Минусы

• Когда добавлена ​​новая опция, более сложно обновить все учетные записи пользователей с помощью новой опции
• Названия опций могут выходить из-под контроля
• Проверка допустимых значений параметров более сложна, для этого необходимы дополнительные метаданные

Вариант 2: Плюсы

• Валидация каждого параметра проще, чем опция 1, поскольку каждый параметр представляет собой отдельный столбец

Вариант 2: Минусы

• Для каждой новой опции требуется обновление базы данных.
• Со многими опциями таблицы базы данных могут стать более трудными в использовании.

Ответ 4

Рассмотрим этот простой пример.

Если у вас есть 2 таблицы, Пользовательский (содержит данные пользователя) и Настройки > (содержит данные настроек). Затем создайте новую таблицу UserSettings для связи с UserTable и SettingsTable, как показано ниже.

Надеюсь, вы найдете правильное решение из этого примера.

Ответ 5

Трудно оценить "лучшее", потому что это зависит от типа запросов, которые вы хотите запустить.

Вариант 1 (обычно известный как "сумка свойств", "пары значений имени" или "сущность-атрибут-значение" или EAV) позволяет легко хранить данные, схема которых вы не знаете заранее. Тем не менее, это затрудняет, а иногда и невозможно, выполнение общих реляционных запросов. Например, представьте, что вы используете эквивалент

select count(*) 
from USER_ALERT_SETTINGS 
where EmailAdded = 1 
and Email_LimitMax > 5

Это быстро стало бы очень запутанным, особенно потому, что ваш механизм базы данных не может сравнивать поля varchar в цифровом смысле (так что " > 5" может не работать так, как вы ожидаете).

Я бы разработал запросы, которые вы хотите запустить, и посмотрите, какой проект лучше всего поддерживает эти запросы. Если все, что вам нужно сделать, это проверить лимиты для отдельного пользователя, мешок с недвижимостью в порядке. Если вам нужно сообщить обо всех пользователях, возможно, это не так.

То же самое можно сказать о JSON или XML - это нормально для хранения отдельных записей, но делает запросы или отчеты для всех пользователей более сложными. Например, представьте, что вы ищете параметры конфигурации для адреса электронной почты "[email protected]" - для поиска адреса электронной почты node требуется поиск по всем XML-документам.