Вопрос по database, sql-server, mysql – Дизайн базы данных для пользовательских настроек

34

Какой из следующих вариантов, если таковые имеются, считается наилучшей практикой при разработке таблицы, используемой для хранения пользовательских настроек?

(ОПЦИЯ 1)

USER_SETTINGS
-Id
-Code (example "Email_LimitMax")
-Value (example "5")
-UserId

(ВАРИАНТ 2)

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

"USER_ALERT_SETTINGS"
-Id
-UserId
-EmailAdded (i.e true)
-EmailRemoved 
-PasswordChanged
...
...

"USER_EMAIL_SETTINGS"
-Id
-UserId
-EmailLimitMax
....

(ВАРИАНТ 3)

"USER"
-Name
...
-ConfigXML

Ваш Ответ

5   ответов
3

которые вы хотите выполнить.

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

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

Это быстро станет очень запутанным, особенно потому, что ваш движок базы данных может не сравнивать поля varchar в числовом значении (поэтому «& gt; 5» может работать не так, как вы ожидаете).

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

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

В качестве альтернативы конфигурация может быть сохранена в базе данных как JSON, XML, что вы думаете об этом? 001
Вы всегда можете запросить, чтобы проверить лимит, прежде чем добавлять новую запись, поэтому это не проблема. 001
64

личных вариантов.

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

Смотрите следующее ERD:

Property Bag ERD

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

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

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

EDIT: Еще несколько деталей, в том числе несколько примеров ...

Обратите внимание, что приведенная выше 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                   |
+------+---------+------------+--------------------------+---------------------+

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

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

EDIT: Example Query

Вот пример 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.

Хорошая диаграмма, ALLOWED_SETTINGS_VALUE, как здесь определены атрибуты? почему не вариант 3? :) 001
@YamiOdymel - Я бы не стал. Если у вас есть факты, которые имеют одно значение (т. Е. Только один правильный ответ за раз), и если эти вещи имеют особое значение в вашей системе (например, аватар, токен, электронная почта, ...), сохраните эти вещи как атрибуты пользователя ( то есть столбцы наUSER Таблица). Не рекомендуется использовать структуру пакетов свойств, если ваше приложение более структурировано.
@PeterPenzov - обновить что? Ваши вопросы слишком расплывчаты, чтобы я мог вам помочь. В целом могу сказать, что в дизайне базы данных нет ничего экзотического, что усложняет обновление записей. Есть ли какая-то причина, по которой вы чувствуете регулярный SQLUPDATE заявления не сработают?
@SarvarNishonboev Я использую Visio с таблицей форм ERD, которую я сам создал некоторое время назад, но вы могли бы использовать общие поля и линии с вороньей меткой «стрелки» & quot; стрелки & quot; на каждом конце. Я также использую пользовательскую текстуру линии, чтобы придать ей нарисованный от руки вид, который, на мой взгляд, полезен в черновых / высокоуровневых набросках, чтобы показать, что это эскиз, а не формальный дизайн.
@ 001 - Нет ничего плохого в варианте 3, если это то, что вы хотите. Это позволяет вам что-то добавить в свои настройки, и это зависит от вашего приложения, чтобы понять это. Он не позволяет вам давать пользователю рекомендации о том, какие настройки доступны / разрешены - по крайней мере, он заставляет это руководство вносить в ваш код, а не управлять им. ALLOWED_SETTING_VALUE, вероятно, будет varchar для значения и varchar для описания / заголовка (необязательно). Фактический тип значения будет соответствовать SETTING.data_type, но для здравомыслия он будет закодирован как varchar.
4

If you have 2 tables, UserTable(contains user details) and SettingsTable(contains settings details). Then create a new table UserSettings for relating the UserTable and SettingsTable as shown below

user settings data base design

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

Это хороший ответ. Это должно быть немного сложнее, чтобы учесть несколько типов данных, возможно.
7

«мешок свойств») прост в реализации - очень мало предварительного анализа. Но у этого есть куча недостатков.

If you want to restrain the valid values for UserSettings.Code, you need an auxiliary table for the list of valid tags. So you have either (a) no validation on UserSettings.Code – your application code can dump any value in, missing the chance to catch bugs, or you have to add maintenance on the new list of valid tags.

UserSettings.Value probably has a string data type to accommodate all the different values that might go into it. So you have lost the true data type – integer, Boolean, float, etc., and the data type checking that would be done by the RDMBS on insert of an incorrect values. Again, you have bought yourself a potential QA problem. Even for string values, you have lost the ability to constrain the length of the column.

You cannot define a DEFAULT value on the column based on the Code. So if you wanted EmailLimitMax to default to 5, you can’t do it.

Similarly, you can’t put a CHECK constraint on the Values column to prevent invalid values.

The property bag approach loses validation of SQL code. In the named column approach, a query that says “select Blah from UserSettings where UserID = x” will get a SQL error if Blah does not exist. If the SELECT is in a stored procedure or view, you will get the error when you apply the proc/view – way before the time the code goes to production. In the property bag approach, you just get NULL. So you have lost another automatic QA feature provided by the database, and introduced a possible undetected bug.

As noted, a query to find a UserID where conditions apply on multiple tags becomes harder to write – it requires one join into the table for each condition being tested.

Unfortunately, the Property Bag is an invitation for application developers to just stick a new Code into the property bag without analysis of how it will be used in the rest of application. For a large application, this becomes a source of “hidden” properties because they are not formally modeled. It’s like doing your object model with pure tag-value instead of named attributes: it provides an escape valve, but you’re missing all the help the compiler would give you on strongly-typed, named attributes. Or like doing production XML with no schema validation.

The column-name approach is self-documenting. The list of columns in the table tells any developer what the possible user settings are.

Я использовал имущественные сумки; но только как предохранительный клапан и я часто об этом сожалел. Я никогда не говорил & # x201C; оу, хотелось бы, чтобы этот явный столбец был пакетом свойств. & # X201D;

4

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

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

Can handle many options New options can easily be added A generic interface can be developed to manage the options

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

When a new option is added, its more complex to update all user accounts with the new option Option names can spiral out of control Validation of allowed option values is more complex, additional meta data is needed for that

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

Validation of each option is easier than option 1 since each option is an individual column

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

A database update is required for each new option With many options the database tables could become more difficult to use
В качестве альтернативы конфигурация может быть сохранена в базе данных как JSON, XML, что вы думаете об этом? 001
У каждого есть своя сила, но вариант 1 лучше всего подходит для большого количества настроек
Похоже, они оба даже .. 001

Похожие вопросы