Сохранение данных массива в нормализованной таблице
Я обновляю свою систему управления пользователями с помощью функции пользовательских настроек. Другими словами, я хочу сохранить пользовательские настройки / параметры в базе данных. Первоначально я рассматривал сериализацию данных и сохранение их в TEXT поле. Но это имеет тот недостаток, что он не доступен для поиска по значению конфигурации.
Поэтому я решил нормализовать данные и сохранить их в другой таблице. Поскольку данные конфигурации многомерны, мне пришлось сгладить данные. Например,
Array (
[user] => Array (
[is_developer] => 1
[api_access] => 1
)
[api] => Array (
[scopes] => Array (
[0] => astro
[1] => user_info
)
[default_scope] => user_info
)
[astro] => Array (
[location_id] => 12345
[timezone] => America/New_York
[coordinates] => Array (
[latitude] => 12.22
[longitude] => 24.44
)
)
)
преобразуется в
+---------+-----------------------------+------------------+
| user_id | config | value |
+---------+-----------------------------+------------------+
| 1 | user.is_developer | 1 |
| 1 | user.api_access | 1 |
| 1 | api.scopes | astro |
| 1 | api.scopes | user_info |
| 1 | api.default_scope | user_info |
| 1 | astro.location_id | 12345 |
| 1 | astro.timezone | America/New_York |
| 1 | astro.coordinates.latitude | 12.22 |
| 1 | astro.coordinates.longitude | 24.44 |
+---------+-----------------------------+------------------+
Есть ли лучший способ для хранения этих данных?
Преимущества:
- Возможность поиска через пользовательские параметры, например, список всех пользователей, которые являются разработчиками и имеют доступ к API.
- Возможность загрузки только одной группы опций.
SELECT * FROM user_config WHERE config LIKE 'astro.%' AND user_id = 1;
Недостатки:
- Невозможно указать уникальный ключ, поскольку числовые массивы имеют ключ сохранения конфигурации. Если я добавлю числовой индекс к ключу (
api.scopes.0,api.scopes.1и т. д.), тогда преимущество № 1 частично теряется. - Поскольку уникального ключа нет, я не могу использовать более эффективный
INSERT ... ON DUPLICATE UPDATEзапрос. Вместо этого, когда необходимо обновить данные, я должен удалить существующие значения и заново вставить данные в транзакцию.
Обновить:
Прочитав ответ unique2, у меня появилась другая идея. Дальнейшая нормализация данных может решить проблему уникального ключа. т.е. сохранить ключи в одной таблице и данные в другой. Нужно проверить, если Вставка не работает с несколькими таблицами, поэтому я думаю, что дальнейшая нормализация не имеет значения.INSERT ... ON DUPLICATE UPDATE будет работать с несколькими таблицами.
mysql> SELECT * FROM user_config;
+-----------+---------+-----------------------------+
| config_id | user_id | config |
+-----------+---------+-----------------------------+
| 1 | 1 | api.default_scope |
| 2 | 1 | api.scopes |
| 3 | 1 | astro.coordinates.latitude |
| 4 | 1 | astro.coordinates.longitude |
| 5 | 1 | astro.location_id |
| 6 | 1 | astro.timezone |
| 7 | 1 | user.api_access |
| 8 | 1 | user.is_developer |
+-----------+---------+-----------------------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM user_config_value;
+-----------+------------------+
| config_id | value |
+-----------+------------------+
| 1 | user_info |
| 2 | astro |
| 2 | user_info |
| 3 | 12.22 |
| 4 | 24.44 |
| 5 | 12345 |
| 6 | America/New_York |
| 7 | 1 |
| 8 | 1 |
+-----------+------------------+
9 rows in set (0.00 sec)
Замечания:
- Хранение данных в базе данных NOSQL не вариант.
- На самом деле, это скорее академический вопрос, потому что реальные хранящиеся пользовательские данные очень малы, и это слишком мало для пользователей. Следовательно, в моем конкретном случае его сохранение в виде сериализованного массива или загрузка всего массива не будет огромными затратами.
2 ответа
Обновление (см. Предыдущую версию ниже):
И с вашим решением, и с моей предыдущей версией на самом деле есть проблема, что не вся информация из вашей структуры массива сохраняется. Вы все еще можете избежать api.scopes.1 поместив последний элемент имени опции в отдельное поле. Если вы объедините это с мягким удалением, вы можете использовать INSERT ... ON DUBLICATE UPDATE,
+----------+-------------------+---------------+------------------+---------+
| user_id* | config_group* | config* | value | deleted |
+----------+-------------------+---------------+------------------+---------+
| 1 | user | is_developer | 1 | 0 |
| 1 | user | api_access | 1 | 0 |
| 1 | api.scopes | 1 | astro | 0 |
| 1 | api.scopes | 2 | user_info | 0 |
| 1 | api | default_scope | user_info | 0 |
| 1 | astro | location_id | 12345 | 0 |
| 1 | astro | timezone | America/New_York | 0 |
| 1 | astro.coordinates | latitude | 12.22 | 0 |
| 1 | astro.coordinates | longitude | 24.44 | 0 |
+----------+-----------------------------------+------------------+---------+
* marks key columns
Предыдущая версия:
Если вы разделите свои данные на две таблицы, вы можете использовать уникальный ключ для каждой из них.
Первая таблица содержит все параметры конфигурации, которые принимают одно значение (* отмечает ключевые столбцы):
+----------+-----------------------------+------------------+
| user_id* | config* | value |
+----------+-----------------------------+------------------+
| 1 | user.is_developer | 1 |
| 1 | user.api_access | 1 |
| 1 | api.default_scope | user_info |
| 1 | astro.location_id | 12345 |
| 1 | astro.timezone | America/New_York |
| 1 | astro.coordinates.latitude | 12.22 |
| 1 | astro.coordinates.longitude | 24.44 |
+----------+-----------------------------+------------------+
Вторая таблица содержит все параметры, которые состоят из набора значений (без дубликатов; * отмечает ключевые столбцы):
+----------+-----------------------------+------------------+
| user_id* | config* | value* |
+----------+-----------------------------+------------------+
| 1 | api.scopes | astro |
| 1 | api.scopes | user_info |
+----------+-----------------------------+------------------+
Таким образом, вы можете использовать свою базу данных для обеспечения целостности данных. INSERT ... ON DUBLICATE UPDATE естественно работает с первой таблицей. Вы также можете использовать его со своей второй таблицей, если вы хотите использовать мягкое удаление:
+----------+-----------------------------+------------------+---------+
| user_id* | config* | value* | deleted |
+----------+-----------------------------+------------------+---------+
| 1 | api.scopes | astro | 0 |
| 1 | api.scopes | user_info | 0 |
| 1 | api.scopes | old | 1 |
+----------+-----------------------------+------------------+---------+
Вы заново изобрели дизайн под названием Entity-Attribute-Value. Это не то, что означает нормализация. Ни предложенные в вашем вопросе идеи, ни предложенное решение из ответа @ unique2 не подходят под определение нормальной формы - это даже не отношения.
Вы правы, что уникальные ограничения не работают. Ни ограничения внешнего ключа, ни даже NOT NULL. Например, как бы вы сделали базу данных принудительно установить обязательное значение для user.api_access?
Дополнительную информацию о том, почему EAV является нереляционным проектом, см. В моем блоге EAV FAIL или во многих моих ответах по Stackru для вопросов об атрибутах-сущностях.
Альтернативные решения для поддержки пользовательских атрибутов см. В моей презентации " Расширяемое моделирование данных". Я дал обзор плюсов и минусов:
- Дополнительные столбцы
- Entity-Attribute-Value
- Наследование таблицы классов
- Сериализованный LOB
- Инвертированные индексы