Несколько идентификаторов в поле. Postgresql

Question

Итак, в настоящее время я работаю с системой баз данных, где пользователь может зарегистрироваться, войти в систему и обновлять свои данные в любое время. База данных включает 5 ролей:

1. Public
2. Member
3. Moderator
4. Coordinator
5. Admin

Я хочу иметь возможность назначать несколько ролей своим пользователям. Например, администратор также может быть участником. Поэтому в базе данных должно отображаться:

User_id    |    Role_ID
------------------------
user1      | 2, 5

^ можно ли добавить многозначные идентификаторы в postgresql?

Answer 1

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

Создайте одну таблицу: role_names и другие роли, вот так:

CREATE TABLE role_names
(
  id serial NOT NULL,
  name text NOT NULL,
  CONSTRAINT role_names_pkey PRIMARY KEY (id),
  CONSTRAINT role_names_name_key UNIQUE (name)
);

CREATE TABLE roles
(
  user_id bigint NOT NULL,
  role_id bigint NOT NULL,
  CONSTRAINT roles_pkey PRIMARY KEY (user_id, role_id),
  CONSTRAINT roles_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role_names (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
);

Теперь в таблице role_names вы поместите все роли, которые хотите иметь. В таблице ролей вы можете назначать или удалять любое количество ролей для любого пользователя. Также вы можете выполнять поиск в таблицах ролей для конкретных пользователей или конкретных ролей - гораздо аккуратнее и быстрее, чем поиск по массивам, я думаю.

Не стесняйтесь добавлять ограничение FK и для поля user_id.

Answer 2

Да, вы можете использовать массив int для хранения списка ролей.

Вот связанный вопрос - Соединительные таблицы против массивов внешних ключей?