Нужны users, chats, messages и таблица memberships с составным первичным ключом (user_id, chat_id). В messages хранят обязательные внешние ключи chat_id и author_id. Чаты Васи выбираются JOIN через memberships; если имена не уникальны, результат относится ко всем пользователям с таким именем. Для конкретного человека используют его ID.
Как описать пользователей, чаты и сообщения таблицами и выбрать все чаты Васи?
Связь пользователей и чатов многие-ко-многим, обязательная принадлежность сообщения одному чату и запрос без предположения об уникальности имени.
Короткий ответ
Что ответить на собеседовании
Подробный разбор
Ответ с пояснениями
Условие. У пользователя есть имя и дата регистрации, у чата — название и дата создания, у сообщения — текст, автор и дата создания. Пользователь может состоять в нескольких чатах, а каждое сообщение обязательно принадлежит ровно одному чату. Нужно описать таблицы и получить (chat_id, chat_name) для пользователя Вася.
Один из вариантов для PostgreSQL:
CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
registered_at TIMESTAMPTZ NOT NULL
);
CREATE TABLE chats (
chat_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
CREATE TABLE memberships (
user_id BIGINT NOT NULL REFERENCES users(user_id),
chat_id BIGINT NOT NULL REFERENCES chats(chat_id),
PRIMARY KEY (user_id, chat_id)
);
CREATE TABLE messages (
message_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
chat_id BIGINT NOT NULL REFERENCES chats(chat_id),
author_id BIGINT NOT NULL REFERENCES users(user_id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
Связующая таблица задаёт многие-ко-многим и запрещает повтор одного членства. Один обязательный chat_id в сообщении обеспечивает его принадлежность одному существующему чату.
Запрос по имени:
SELECT DISTINCT c.chat_id, c.name AS chat_name
FROM users AS u
JOIN memberships AS m ON m.user_id = u.user_id
JOIN chats AS c ON c.chat_id = m.chat_id
WHERE u.name = 'Вася'
ORDER BY c.chat_id;
DISTINCT убирает повтор чата, если в нём несколько пользователей с именем Вася. Имя не объявлено уникальным, поэтому для конкретного человека правильнее фильтровать user_id, переданный параметром.
Из условия не следует, что автор обязан оставаться участником чата навсегда. Проверку членства в момент отправки и сохранение истории после выхода нужно согласовать отдельно. Политику удаления тоже нельзя молча заменить каскадным уничтожением всех сообщений.