Сопоставление строк отношениям
Я создаю систему, в которой задачи могут быть назначены разным пользователям. Проблема в том, что задачи сопоставляются через строковый столбец с именем получатель, который в конечном итоге сопоставляется с набором пользователей. Содержимое этого столбца может выглядеть так:
has:tasks-update,tasks-access— Пользователи с разрешениемtasks-updateиtasks-access.role:administrator- Пользователи с ролью администратора.
Вот сейчас проблематично решаю. Это несколько просто, когда мне нужно выяснить, кто имеет доступ к конкретной задаче, но обременительно, когда пользователю нужно знать, какие задачи ему «назначены». Прямо сейчас я разрешаю каждый столбец получателя, чтобы увидеть, включен ли пользователь, это, к сожалению, не очень осуществимо, так как это связано с огромными затратами на производительность.
У меня уже есть индексы для соответствующих столбцов, чтобы ускорить поиск.
Решением этого было то, что я разрешал получателей, когда получатель был изменен, а затем помещал отношения между пользователями и задачами в промежуточную таблицу. Хотя это позволяет мне быстро искать задачи, которым назначен пользователь, это также становится проблематичным, поскольку теперь мне нужно отслеживать (например) каждый раз, когда пользователю была назначена роль администратора, и теперь синхронизировать это с промежуточной таблицей.
Я надеялся, что смогу получить некоторое представление о решении этой проблемы, не жертвуя производительностью, как сейчас, но при этом мне не нужно все время синхронизироваться.
Возможно связано: Так ли уж плохо хранить список с разделителями в столбце базы данных?
Время прочитать опубликованный академический учебник по информационному моделированию, реляционной модели и проектированию БД. (Руководства по языкам и инструментам для записи и использования проектов не являются такими учебниками.) (Как и статьи вики или веб-публикации.) Десятки опубликованных учебных пособий по информационному моделированию и проектированию баз данных доступны онлайн бесплатно в формате pdf. На stanford.edu есть бесплатный онлайн-курс. (Но запрашивать ресурсы вне SO не по теме.)
Ответы 3
Проверенный и хороший подход, соответствующий нормальные формы, состоит в том, чтобы иметь таблицы task_type и role. У вас, конечно, есть таблица пользователей, и, поскольку у пользователя может быть много ролей и привилегий, вам понадобятся таблицы user_role и user_privivity для обработки отношений «многие ко многим». Простой способ справиться с проблемами — иметь несколько чисел, представляющих некоторые привилегии и роли, например 1 для администратора и 2, 3, 5, 7, 11, 13, 17 и т. д. для других привилегий. Наличие аналогичного номера для роли в качестве первичного ключа облегчило бы проблему сопоставления ролей. Например, рассмотрим случай, когда у вас есть привилегия с кодом 7. Если вы будете искать роли с id, кратным этому коду, то вы получите 7 (data_read, например) и 1 (администратор).
Вам обязательно нужна таблица отношений между пользователями и задачами, и, конечно, в этом отношении вы также должны отметить, является ли пользователь администратором или нет. Это лучший способ спроектировать структуру вашего приложения вместо того, чтобы объединять информацию в один столбец, что вызывает проблемы с производительностью/сложностью. Продолжайте в том же духе, ваша работа только выиграет от этого.
Хранение списка чего-либо в виде строки в единственном столбце может привести к всевозможным проблемам в будущем.
Как вы уже столкнулись... любые реляционные операции поиска, вставки, обновления или удаления в списке сначала потребуют некоторой формы синтаксического анализа существующего списка.
Стоит отметить, что любые индексы в этом столбце, скорее всего, НЕ будут использоваться движком для этих задач, поскольку индексы в столбцах на основе строк (кроме FULL TEXT) действительно полезны только при поиске в начале строки.
Например,
SELECT *
FROM site_user
WHERE recipients LIKE '%tasks-update%'
Не сможет использовать индекс в столбце получателей
Предложение
Я бы разделил ваши текущие списки на новые таблицы, например
role- идентификатор, имя, …- например {3, «администратор»,… }
permission- идентификатор, имя, …- например {5, «доступ к задачам»,… }
- например {9, «обновление задач»,… }
site_user- идентификатор, имя, role_id, …- например {7, 'Джефф', 3,…}
site_user_permission- идентификатор, site_user_id, разрешение_id, …- например {1, 7, 5,…}
- например {2, 7, 9,…}
Где из примеров записей «Джефф» является «администратором», и ему были назначены разрешения «обновление задач» и «доступ к задачам».
Поиски должны быть легко достижимы с помощью JOIN и оставаться согласованными при добавлении или удалении данных. Целостность данных можно поддерживать, добавляя соответствующие внешние ключи и уникальные индексы.
Н.Б. Без конкретных примеров операций, которые вызывают у вас проблемы, или более подробной информации о том, как вы собираетесь использовать роли и разрешения пользователей, трудно сделать что-то большее, чем дать общие рекомендации.
"Огромная стоимость производительности"? Насколько велика (приблизительно) каждая таблица? Сканирование таблицы с миллионом строк было бы плохим; это то, что у вас есть?