Здравствуйте, товарищи. Возник интересный вопрос, на который не нашлось толкового

ответа.

Допустим планируется БД для некоторого мессенджера, похожего на Telegram

В этом мессенджере присутствуют такие штуки как юзеры, боты и конференции. У всех них есть юзернейм, который должен быть уникальным, т.е. один юзернейм не может быть назначен и юзеру и боту (так же как и боту и коефе, и юзеру и конфе).

Как такое выразить в схеме БД?


Самым простым решением пока что кажется ввести такую таблицу:

names:
username: str PK
user: int NULLABLE FK users.id
bot: int NULLABLE FK bots.id
conference: int NULLABLE FK conferences.id

И добавить ей триггер на вставку и обновление, проверяющий, соответственно что два из трёх полей user, bot, conference равны NULL

если уж выделять отдеьлную таблицу то зачем вам на каждый тип сущности отдельное поле?

А как иначе? Инфа о юзерах, ботах и конференциях разнесена по разным таблицам, т.к. это всё разные сущности

так у вас в разных таблицах и будет ссылка на таблицу с username и все, из самого факта связи понятно что за сущность, нет?

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

Можно и не триггер, а CHECK. А зачем вам разные нэймспейсы айдишников ботов и юзеров? По-моему, это тожэ путь к проблемам.

Да и конференцый тожэ. Без этого можно было бы сделать просто поле type.

Разные id'шники в целом не так уж критичны А вот с юзернеймами - беда

Ну, некритичны -- это ладно. Я спрашываю -- а зачем вообще так делать? От этого видно сразу несколько проблем (куча API должны будут получать id и тип, все акторы этих API должны будут смотреть в какие-то unionы таблиц) -- и не видно каких-то плюсов.

> И добавить ей триггер на вставку и обновление Для этого хватит и CHECK. > Как такое выразить в схеме БД? Ещё вариант — "перевернуть" предложенную схему, т.е. сделать: entity(username PK /* ну или использовать cуррогатный ключ, по сути это неважно*/ ); users(username FK entity, ...); bots(username FK entity, ...); conferences(username FK entity, ...); Тут нужен триггер для проверки того, что entity — это что-то одно (и, возможно, только одно).

"Зачем так делать?" это про юзернеймы? Если да то в целом мне такое кажется весьма удобным. Т.е. юзернейм по сути служит некоторым алиасом. Про проблемы не совсем понял, можно подробнее?

Это про разные id у ботов, пользователей и конференцый.

у выше упомянутого телеграмма id кстати по моему уникальные как минимум для чатов и юзеров

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

И прямо в entity вписать (username, type), эту жэ пару использовать как FK. в таблицы users,bots,conferences вписать ещё CHECK (type=)

Если не выкручиваться каким-то образом, то да. Но и CHECK-и (ну или триггеры) в "Вашей" модели Вы тоже будете вынуждены менять в таком случае, кстати.

Зачем? Так хочется нарушить 2NF на ровном месте? ;)

Ну чисто субъективно выглядит проще поменять триггер/чек в одной таблице чем в нескольких

Чтобы не писать ни одного триггера.

Не писать ни одного триггера чтобы что?

У нас своя атмосфера, не обращая внимания.

В телеграме, емнип, id'шники юзеров и чатов различаются знаком Насчёт ботов - не знаю, не уточнял

Зато придётся тупо писать 'bot' в каждую запись таблицы "bots" и т.д. (да и переименовывать таблицы, если что, тоже может быть проблематично), что не очень приятно. Но тут каждое решение не без недостатков, это да.

Не придётся, там default будет.

Да в целом не видится страшным иметь в API разные эндпоинты для получения инфы о юзерах/ботах/конфах Наоборот было бы странно видеть один общий эндпоинт для всей своры

Получение инфы-то ладно. Она и правда сильно разная. Со списками сообщений и прочими линками/перепостами -- всё веселее.

Хранить придётся, я имел в виду (и это довольно тупо, IMNSHO). Но, опять-таки, тут каждое решение не без минусов.

Это да. Что ещё хужэ -- потребуется дополнительный индэкс для fk entity, кроме pk.

Можно денормализовать не на пустом месте, если в таблице с юзернеймами так же хранить name, surname и title (первые - для юзеров, третье для ботов и конф), так как это существенно упростит поиск...

Для сообщений так же планирую разные таблицы Как минимум разделить личные сообщения и сообщения в конференциях, т.к. в конференциях ещё будет ролевая модель с разрешениями

у бота есть токен

Который средство аутэнтификацыи и потому меняется по много раз за жызнь бота.

Токен имеется и у юзеров (access token'ы же). Но вообще да, с токенами будет неприятно, т.к. придётся и их разделять... Больно, очень больно

Хотя падажжи. У бота токен же только один. А значит его можно определить в таблицу bots и никаких проблем не будет.

Не знаю, я многоботовую базу не делал, только для одного бота одна база.

Ну и при том ботам можно сделать отдельный API, чем решится множество проблем

И это ещё добавит триггеров (для синхронизации этой информации). Кстати: > так как это существенно упростит поиск... или усложнит — зависит от того, какой именно поиск. Я к тому, что денормализация — это tradeoff (т.е. что-то в итоге работает хуже) — вопрос в том, что важнее.

Синхронизация не понадобится Я подразумеваю не копировать информацию об имени/тайтле, а хранить её только этой таблице, вместо хранения раздельно в таблицах юзеров/ботов/конференций) А про поиск... Не думаю что селект по одной таблице будет сложнее и менее выгоднее селекта по сджоненным таблицам)

> Синхронизация не понадобится А, ясно. > Не думаю что селект по одной таблице А зря (её размер-то тупо больше). Т.е. зависит от запросов, в любом случае ("JOIN-ы дорогие" и т.п. — это просто мифы). Кстати... вот посмотрел я ещё раз на оригинальную модель. Она же (грубо говоря) вот такая, правильно? CREATE TABLE users ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, other_stuff text ); CREATE TABLE bots ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, other_stuff text ); CREATE TABLE conferences ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, other_stuff text ); CREATE TABLE names ( username text PRIMARY KEY, "user" int REFERENCES users, bot int REFERENCES bots, conference int REFERENCES conferences, CHECK (num_nonnulls("user", bot, conference) = 1) ); Тогда что мне мешает сделать вот так, например INSERT INTO names(username, "user", bot, conference) VALUES ('username1', 1, NULL, NULL); INSERT INTO names(username, "user", bot, conference) VALUES ('username2', 1, NULL, NULL); ? Т.е. в ней тоже как-то не хватает constraints... нет?

Да, получается надо ещё навесить UNIQUE на user, bot и conference