Робота з базою даних: щоденне повідомлення для кожного користувача

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті.

Припустимо, є якийсь веб-застосунок (PHP & MySQL), в якому потрібно додати новий функціонал. Кожного дня, коли користувач вперше відкриває url застосунку, йому повинно відобразитися спеціальне повідомлення. Нехай це буде передбачення, як у чеках Сільпо. Усього таких повідомлень 300. Кожного дня потрібно виводити нове повідомлення, тобто вони не повинні повторюватися. Як повинна виглядати база даних у цьому випадку?

Мій варіант:
— таблиця wishes, у котрій будуть повідомлення
— таблиця user_wishes, у котрій будуть поля user_id та wish_id для зв’язку між користувачем та відправленим повідомленням
— у таблиці users додати поле wished_at для оновлення часу останнього відправлення повідомлення

Що мені не до вподоби у такому варіанті:
— під час кожного запиту додасться перевірка поля wished_at та порівняння його з поточною датою, що якось тупо
— якщо активних користувачів багато, а повідомлень не 300 а 3000, то протягом року таблиця user_wishes стане занадто великою, вибірка стане повільнішою (мені так здається)
— під час вибору яке саме передбачення відправити, потрібно отримати id вже відправлених та зробити вибірку із усіх існуючих крім вже відісланих, це також таке собі рішення, не подобається

Можливо є якісь книги, статті або відео-курси с прикладами проектування БД? Тольки не рівня «let’s make database for our blog» а щось нормальне.

PS. Переписав солов’їною. Питання суто теоретичне. Хочу розібратися з проектуванням БД під різні задачі, от вигадую собі невелики завдання, та намагаюся їх обміркувати. Як ви помітили, поки не дуже виходить.

Теми: MySQL, PHP, tech, розробка

TABLE users (id, .....) TABLE messages (id, ....) TABLE message_to_user( id, user_id, message_id, time, FK(user_id) REFERENCES users(id), FK(message_id) REFERENCES messages(id))

Ctrl + Enter

Андрій Гордєєв 26.08.2023 15:17

Колись давно робили подібне для видачі персам групи квестів у випадковій послідовності. В БД взагалі нічого не зберігалося. Після авторизації робиться спроба додати ключ в мемкеш на добу. Якщо вийшло (перший візит, старого ключа вже нема), то по функції псевдовипадкових чисел, з ключем id юзера, визначався номер квеста.

Відповісти

Підтримати

Ярослав Характерник Golang Developer в DocHQ 30.04.2023 21:41

По-перше є drawsql.app/templates

По-друге ви можете локально створити потрібний стан БД й протестувати швидкодію dou.ua/forums/topic/40760

Mukola Derevlo 29.04.2023 01:08

а навіщо зберігати дату показу?
якщо ви можете додати до таблиці user поле, то достатньо додати wishes counter 0-299
і створити таблицю wishes
коли юзер авторизується він всерівно отримує інформацію з бази про себе, де буде вказаний останній порядковий номер побажання. після показу номер оновлюється, або при отриманні інформації про користувача.
якщо не можна чіпати таблицю user,
можна створити user_id_wish_counter і писати туди.
а якщо треба рандом( я давно не юзав mysql там type array нема поідеї, потрібно се(десе)ріалізувати, json через костиль точно був) можна зберігати counter в вигляді массиву чисел від 0 до 299 і просто видаляти з массива показаний id-номер

ps. на рахунок проектування тут тільки з досвідом, бо завжди знайдеться хтось з іншою точкою зору, або навіть ти сам через якийсь час, який гляне і скаже «нафіга так ускладнювати можна ось так»
мені mongodb після mysql набагато цікавіше зайшла, хоч це зовсім інше пальто, але в плані зручності дуже круто

Олександр Мощенко 01.05.2023 15:34

як з counter зрозуміти, що сьогодні побажання вже надсилалося?

Mukola Derevlo

Mukola Derevlo 02.05.2023 12:25

я не бачив таблиці users, але зазвичай там має бути timestamp для update, якщо нема а ми вже і так міняєм таблицю user то можна і додати.(можливий побічний еффект якщо буде мінятися інша інформація в таблиці user то дата останнього показу буде не зовсім актуальна)
можна юзати кукі, можна локал сторідж. можна писати в тей же counter конкатенуючи time. можна додати поле окреме для дати останнього показу + юзати кукі. але я думаю ви і так це все знаєте, просто захотіли порофлить

Олександр Мощенко

Олександр Мощенко 02.05.2023 13:16

щось трошки знаю, просто зачепився за

а навіщо зберігати дату показу?

. Думав може є якийсь алгоритм, що дозволить позбутися усяких «дата останнього повідомлення» абощо. Ну або я невірно зрозумів ту фразу, це теж цілком може бути

Dmitry Bugay Java Team Lead 28.04.2023 13:40

а повідомлень не 300 а 3000, то

...нічого не станеться. Про це треба думати якщо повідомлень буде, ну скажім, 30 000 000.

— у таблиці users додати поле wished_at для оновлення часу останнього відправлення повідомлення

Оце помилка дизайну. Час останнього показу повідомлення — це час настання останнього історичного факту показу повідомлення з-поміж багатьох таких історичних фактів.

SELECT TOP 1 time
FROM message_to_user
WHERE user_id = ?
ORDER BY time DESC

— під час кожного запиту додасться перевірка поля wished_at та порівняння його з поточною датою, що якось тупо

Да, тупо. Бо це поле 1) не має існувати 2) не потрібно для логіки

це також таке собі рішення, не подобається

Це єдине вірне рішення, якщо ти хочеш мати рішення в рамках реляційної БД.
Інші рішення автоматично переходять в ранг дивних і шкідливих велосипедів, бо будуть порушувати філософію реляційної БД і не будуть найпростішим і найочевиднішим рішенням. Щоб показати користувачу повідомлення, якого він ще не бачив, достатньо:

SELECT TOP 1 
FROM messages
WHERE id NOT IN (
     SELECT message_id
     FROM message_to_user
     WHERE user_id = ?
     )

...якщо не повернуто жодної строчки — юзеру показані всі повідомлення
...якщо у повідомлень є якийсь ордер, то його можно додати прямо в цей запит, просто дописавши в кінець ORDER BY %condition%

Якщо ти НЕ хочеш робити це на реляційній БД — то вся тема нерелевантна.

Хочу розібратися з проектуванням БД під різні задачі, от вигадую собі невелики завдання, та намагаюся їх обміркувати.

Твоя проблема зараз в тому, що ти дізнався про існування БД але ще не зрозумів їхньої філософії, тому ти просто якось розташовуєш дані в базі і не можеш оцінити наскільки вони добре розкладені.
Намагайся спроектувати БД так, щоб кожна таблиця зберігала щось 1 з 3:
— сутність, мутабельна, незалежна від інших сутностей, не містить посилань на інші сутності, не містить даних, що походять від інших сутностей або подій;
— перелік історичних фактів (подій), імутабельні, можуть посилатися на інші сутності;
— зв"язки між сутностями або історичними фактами (джойн таблиці по суті), імутабельні, можуть посилатися на інші сутності

Quercus Alba 28.04.2023 14:37

Круто. Дуже дякую за відповідь. Скажіть, а от стосовно ось таких речей:

Намагайся спроектувати БД так, щоб кожна таблиця зберігала щось 1 з 3

Де ви берете такого роду інформацію? Вона із покоління в покоління передається, чи є якась must have література по БД та системному дизайну?

Dmitry Bugay

Dmitry Bugay Java Team Lead 28.04.2023 15:17

Це комплексне питання.

В ідеалі, це мали б викладати в університеті на технічних спеціальностях. Але в Україні системна криза з освітою, в т.ч. з технічною.
Крім того, я світчер-самоучка, що в універі вчився гуманітарщині, тому до цих речей доповзав сам, через досвід. Але не виключаю, а скоріш впевнений, що вся ця мудрість давно викладена десь в старих товстезних книгах. Які я нажаль не читав )
А досвід складається з багатьох джерел. Це і твої власні помилки, і помилки колег, настанови, коментарі, срачі, холівари в інторнетах і т.д.

Але все це спирається також на тренування внутрішнього відчуття систематизації і порядку. Чого набувають не всі і це не вичитаєш в книзі.

Але щодо книг, то оця хороша, хоч і не безпосередньо по БД, але поряд:
books.google.com.ua/...hl=uk#v=onepage&q&f=false

Quercus Alba

Quercus Alba 28.04.2023 16:23

Ще раз дякую, ваші відповіді виглядають корисними.

Дмитро 28.04.2023 09:28

Це проблема не проектування бд, а вірного системного дизайну. Наприклад, щоб не спамити бд різними запитами і не виконувати всю логіку на кожен виклик і не вантажити cpu, розраховуйте в якомусь кеші 300 повідомлень по дням для кодного юзера(в момент створення або в джобі) складайте в якийсь кеш з ключем user-date і просто покузайте ваші повіденлення простою операцією get на кожен виклик без логіки будь якої додаткової і операцій запису непотрібних.

Quercus Alba 28.04.2023 13:43

Дякую. А чи є у вас рекомендації що до книжок або курсів по системному дизайну?

Дмитро

Yuriy Nesteryuk freelancer 28.04.2023 23:41

Designing Data-Intensive Applications.
Web scalability for startup engineers
Site reliability engineering
Designing distributed systems
System Design Interview — An insider’s guide

Valeriy Shvets Engineer в Phonexa 28.04.2023 08:22

Норм варіант, все по класиці, одним sql-запитом отримується wish для кліента.
База даних і створена щоб працювати з даними, головне індекси правильно розставити.

Коли вже даних чи запитів від одного користувача в день буде багато, то можна ускладнювати — зберігати в кеші user_id->date, щоб не робити багато запитів до бази для одного юзера за добу. Або більш складні варіанти, зберігати в кеші список wishes та якийсь час дані останніх активних користувачів і робити дві, в тому ж redis’і тип Sets. Але це треба робити не відразу, а коли вже є реальна необхідність, розуміння про об’єми даних, прогнози зростання та інфраструктуру проекта.

Quercus Alba 28.04.2023 14:19

Але це треба робити не відразу, а коли вже є реальна необхідність, розуміння про об’єми даних, прогнози зростання та інфраструктуру проекта.

Дякую, це треба запам’ятати.

Valeriy Shvets

Василь Коваль 27.04.2023 17:55

Що робити коли «пожеланія» скінчаться і більше не буде нових? Чи можуть різні користувачі бачити однакові «пожеланія»?

Quercus Alba 27.04.2023 20:03

Коли буде показано останнє повідомлення, вони просто перестануть показуватися. Можливо для цього потрібно ще одно поле у users, яке після останнього повідомлення стане false, чи якось так. Кожен із користувачів обов’язково побачить усі 300 повідомлень. Тобто вони однакові для всіх.

Василь Коваль

Василь Коваль 28.04.2023 09:55

От і відображайте всі побажання по порядку з першого до останнього для кожного користувача. Зберігайте десь для користувача айди побажання та дату коли воно застосовувалось. Якщо дата сьогоднішня — використовуєте цей айді. Якщо інша — берете наступне айді та оновлюєте дату на сьогоднішню.

Quercus Alba 28.04.2023 14:00

От і відображайте всі побажання по порядку з першого до останнього для кожного користувача.

Якщо продовжити аналогію з чеками із Сільпо, то для мене та для покупця на сусідній касі буде надруковано одне і те саме передбачення. Тобто ми, як два юзери, отримали одне і те саме повідомлення. Ну збіг, таке буває. Але виходить, що для усіх, хто був в магазин кожного дня за останню неділю, коли і почали друкуватися передбачення, вони будуть однаковими.

Василь Коваль 28.04.2023 14:05

В реальних системах зазвичай доводиться чимось жертвувати

Quercus Alba 28.04.2023 14:13

Цікаво. Зрозумів вас, дякую.

Дмитро Дем'яненко BA/UX 28.04.2023 13:39

в один день всі бачать одне і теж сповіщення? або сповіщення йдуть з 1 по 300 відносно юзера (я почав сьогодні, мені 1, хтось почав вчора — йому сьогодні 2)?

Quercus Alba 28.04.2023 13:49

Кожен юзер баче рандомне сповіщення. Звісно, у декого вони будуть співпадати, але то випадково. Тобто і я і ви, як два юзери, обов’язково побачимо всі 300 повідомлень, по одному в день. Але послідовність, швидше за все, буде різною. Якось так.

Дмитро Дем'яненко

Ярослав Характерник Golang Developer в DocHQ 27.04.2023 17:49

Чий Крим?

Quercus Alba 27.04.2023 19:59

Український. Завжди був, завжди буде. Ну добре, «завжди був» — це з 91.

Ярослав Характерник