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

#mysql #data_structures

Помогите пожалуйста разобраться в следующей ситуации:

Есть два вида API где хранятся истории сообщений, это Zopim и Chat2Desc(импортировать
в Postman) . Пока эти два но могут потом и другие появится.

И моя ДБ с таблицей users:

Table users
id , email, phone, ...


В Zopim пользователи идентифицируются через email, a в Chat2Desc через телефон. Для
меня эти два поля важны, какой бы чат  не был и сколько бы их не было.

То есть если я получаю емайл либо телефон пользователя в сообщениях, то делаю запрос
в свою базу (table users) для идентифицирования своего пользователя.

Да и в принципе даже структура чатов не важна, я данные как нибудь да выберу.А вот
как их правильно сохранить , да так чтоб у меня была одна структура для всех .

И вот что я придумал:


Разъяснение:

Таблица chats (Данные для чата) :


client_id   - указывает на id таблицы chat_clients
duration    - длительность чата
system_type - хранит имя чата (Zopim, Chat2Desc, ... )
created_at  - дата создания 


Таблица chat_clients (сведений об пользователей которые были в чате):


assigned_data - те инициалы под которыми пользователи были в чате
is_agent      - (0 | 1): 1 => мой пользователь, 0 => не мой
users_id      - id пользователя. Содержит либо id из таблицы users либо пустой.
bean_module   - неважно (сведение о моём пользователе)
unique_col    - Тут будет либо email (из Zopim) либо телефон (из Chat2Desc, Либо
думаю хранить id таблицы users).Будет гарантировать уникальность значений.


Связка users_id + unique_col уникальна (UNIQUE KEY user_id_unique_col_UQ (user_id,unique_col))

Таблица chat_messages:


text      - текст сообщения.
client_id - указывает на id таблицы chat_clients
chat_id   - указывает на id таблицы chats
file_id   - указывает на id таблицы chat_files
transport - значение будет для Chat2Desc (Viber, WhatsApp ,...), для Zopim ,чтоб
не пустовал , Zopim


Таблица chat_files Сведения о переданных файлах в чате.Aналогичных таблиц может быть
может нет для хранения дополнительной инфы.


  Доп инфо: В дальнейшем собираюсь для каждого пользователя выводит
  историю сообщений.


Вопрос:
Как создать гибкую схему таблиц для хранения сообщений из разных чатов ?

Заранее благодарю.
    

Ответы

Ответ 1

Любые проблемы по созданию БД нужно разбивать на две части:


Нужно выделить то, что уже есть. Выделить данность, реальность. То, что вы не можете
изменить. То есть, выделить структуру внешних данных.
Нужно выделить то, что вы хотите получить. Желаемый вид и форма.


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

Затем нужно выделить то, что вы хотите получить. Значить вам нужна какая-то таблица
свя­зки чатов и пользователей, и таблицы связки чатов в основной таблице и чатов в
мате­риаль­ных таблицах. Если нужно хранить сообщения в каждом чате для быстрого доступа,
то лучше будет это сделать явно, в отдельной таблице, не связанной с материальным пред­став­лением.
Так сообщения будут храниться два раза, но вы не будете связаны материальным пред­став­лением
после импорта сообщений, и ваш код получения данных из БД будет много проще и надёжней.

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

Сама сложная структура БД может представлять и сложность при масштабировании. Например,
шардинг и уникальные индексы идут по разные стороны улицы: вы не можете использовать
шардинг одновременно с уникальными индексами. То же можно сказать про скорость вставки
записей: уникальные индексы ей не помогают.