Есть 3 сущности - пользователь, чат, сообщение

Условие

Есть 3 сущности - пользователь, чат, сообщение

• У пользователя есть имя и дата регистрации • У чата есть название и дата создания • У сообщения есть текст, автор и дата создания • Пользователь может состоять в нескольких чатах одновременно • Сообщение обязательно принадлежит чату, сообщение не может принадлежать более чем 1 чату одновременно • Нужно описать предметную область в виде таблиц

-- Выбрать все чаты пользователя Вася в формате (chat_id, chat_name)

Ответ

Нужны users, chats, messages и связующая таблица chat_members для участия пользователя в нескольких чатах. Сообщение содержит один обязательный chat_id и author_id.

Пример схемы PostgreSQL:

CREATE TABLE users (
  id bigint PRIMARY KEY, name text NOT NULL,
  registered_at timestamptz NOT NULL
);
CREATE TABLE chats (
  id bigint PRIMARY KEY, name text NOT NULL,
  created_at timestamptz NOT NULL
);
CREATE TABLE chat_members (
  user_id bigint REFERENCES users(id),
  chat_id bigint REFERENCES chats(id),
  PRIMARY KEY (user_id, chat_id)
);
CREATE TABLE messages (
  id bigint PRIMARY KEY,
  chat_id bigint NOT NULL REFERENCES chats(id),
  author_id bigint NOT NULL REFERENCES users(id),
  body text NOT NULL, created_at timestamptz NOT NULL
);
SELECT DISTINCT c.id AS chat_id, c.name AS chat_name
FROM chats c
JOIN chat_members cm ON cm.chat_id = c.id
JOIN users u ON u.id = cm.user_id
WHERE u.name = 'Вася';

Имя может быть неуникальным: для конкретного пользователя лучше фильтр по id. Правило «автор состоит в чате» и политика удаления участников требуют отдельного решения, чтобы не нарушить историю сообщений.