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

Question

[Кирилл Гладиков]

Я разрабатываю базу данных, в которой будут храниться пользователи разных типов. В настоящее время существует всего два типа пользователей, клиенты и агенты. В дальнейшем, возможно, будет добавлен тип “организация”, которая будет создавать и управлять агентами. Так же в системы присутствуют администраторы, реализованные через роли.
Идея состоит в том, чтобы иметь users таблицу, отвечающую в значительной степени исключительно за вход на сайт ( email и password, token и другие технические поля), и дополнительные таблицы для каждого из соответствующих типов пользователей, которые имеют свой собственный уникальный набор столбцов. Например, у агентов в дальнейшем появятся поля специальность, опыт, дипломы и тд. У клиентов в данный момент нет уникальный полей, но об этом дальше. Однако, поскольку я раньше не управлял базой данных такой сложности, мне интересно, как организовать пару аспектов:

1. Во-первых, пользователями могут быть любой из вышеперечисленных типов. Например, если пользователь авторизован как агент, он все равно должен иметь возможность оформить заказ.
   
2. Во-вторых, необходимо предусмотреть возможность создания клиентов или агентов без создания учетной записи пользователя. Например, если клиент связался с компанией по телефону или отправил электронное письмо, не зарегистрировавшись на сайте. Также возможно добавление типа "организация", где администратор организации сможет создавать агентов с возможностью или без возможности входа в систему (сохранение записи в таблице agent, без записи в user).
   
3. В-третьих, прошу обратить внимание на таблицу “appointment”. В таблице содержаться FK для клиентов и агентов, а не user.
   

Основные проблемы, которые у меня возникают

1. Дублирование основных данных ( first_name, last_name ) у клиента и агента, так как эти данные хранятся в user, а мне нужно иметь возможность создавать клиента и агнета без user
   
2. Сомнения в надобности таблицы “клиент”, так как в данный момент в ней нет дополнительных полей и это усложняет систему.
   

Вот что получилось на данный момент

Comments

[Adamos]

А зачем user - логике, отвечающей за авторизацию - поля first_name и last_name, никакого отношения к авторизации не имеющие и не являющиеся исключительной информацией этой сущности (раз у вас могут быть персональные данные без пользователя)? Отделите и не мучайтесь.

[Maksim B]

Клиент и агент совершенно разные сущности и переживать о дублировании полей тут нет смысла.

В последствии, они вообще могут быть разделены на разные БД и разные сервисы для работы с ними.

[Кирилл Гладиков]

Adamos, Спасибо за комментраций. Изначально я просто хотел выделить общии данные для клиента и агента в user. Если отделить, то в сценарии где изначально пользователь зарегистровался как agent, а в последствии зашел на клиентскую часть у него не будет данных. Или я что то не понимаю?

[Кирилл Гладиков]

Maksim B, Спасибо за комментрарий. Посмотри мой ответ Maksim B

[Adamos]

Кирилл Гладиков, я бы предложил для начала переименовать таблицу user в auth, например, чтобы избавиться от ложных представлений. А user_data - в personal_data и не прибивать ее гвоздиком к регистрации или пользователю, а свободно связать через поле в той таблице, которой эти personal_data нужны. Так юзер, агент и клиент могут обладать одними и теми же персональными данными, раз они у вас могут быть одним человеком. Или агент сможет иметь их, не будучи пользователем вообще.

Answer 1

[Jack444]

Таблицы client/agent/user_data можно в одну таблицу user упаковать, + добавить поля is_client bool и is_agent bool, но так как вы уже сделали связь к role то перекидывайте туда роли client и agent, если возможно использование нескольких ролей для пользователя то используйте не name varchar a names varchar[] чтобы поле было массивом.
Ещё обычно создаётся таблица permissiion которая привязывается к ролям и юзерам + дополнительно хранит поля table_name и action, либо вместо таблицы типом JSONB добавить ячейку в роли и юзеры.
Вообще postgre изначально умеет работать с пользователями и можно использовать такие фичи как CREATE USER и ALTER ROLE но такой вариант может оказаться костыльным и логику с правами лучше перекинуть на уровень приложения.

Comments

[Кирилл Гладиков]

Спасибо за ответ. А где в таком случае лучше хранить данные для входа в аккаунт? Мне просто нужна возможность создавать клиентов и агентов без регистрации отдельных пользователей.

[Jack444]

Кирилл Гладиков, так просто в user сохраняй а пароль хешируй и храни его как хеш в бд, простой вариант использовать функцию md5() встроенную в постгре но md5 уже пробрученный милионы раз, лучше уровнем выше другим каким-нибудь алгоритмом хешировать.
При входе также полученный пароль будет хешироватся и отправлятся запрос в бд на извлечение строки юзера и групы с условием поиска по паролю и емайлу или никнейму, юзер должен естественно должен возвращятся без пароля, если вернулся пустой ответ значит пользователь не существует и отправляем уведомление на фронт кодом 401 например, когда пользователь найден упаковываются данные в куку в виде JWT/Base64 или ключа для локального кеша и возврящем код 200. На всех последующих запросах извлекаем куку а из неё данные пользователя, проверяем исходя из ендпоинта если право у такойто роли им пользоватся, если есть то генерим sql и отправляем в бд если нет то сразу на фронт отправляем код 403..
Тамблица time_zone может быть излишним, через javascript легко получить часовой пояс, отправить его на сервер и присвоить юзеру куку с tz.