Вхід і реєстрація
Розробка · 16 липня 2020, 11:47 2760
Andrii Uhryniuk, dev в myself

Питання по структурі БД

Усім привіт. Хотів би попросити поради щодо структури БД для одні’ї задачі.
Опис задачі:
Є користувачі і категорії, кожен користувач може бачити та додавати категорї тільки для себе.
Категорій може бути необмежене число. Кожна категорія має довільні поля, які створює користувач, полів може бути до 100. У категорію, користувач завантажує файли, їх може бути безліч.
Як правильно організувати структуру таблиць в БД (mysql)?
Поки що реалізовано щось схоже на EAV. Тільки entity_id зберігається в одній таблиці з value.
Чи є сенс створювати окрему таблицю з довільними полями для кожної папки? Чи може перевести на NoSQL?
Як правильно організувати структуру таблиць в БД (mysql)? Дякую.

UPDATE
Вибачаюсь,. Забув написати ще умови: фільтрування та сортування.
Сортування по одному полю.
А от щ фільтрів може бути як 1 так і 20. В такому випадку буде багато джойнів, оскільки вибірка може бути тільки через AND.

UPDATE2
Усім дякую. Підключив ElasticSearch.

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

Теми: БД
👍ПодобаєтьсяСподобалось0
До обраногоВ обраному1
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Gregory Bocharov Senior Software Developer 19.07.2020 17:27

Насколько я понимаю, речь идет о проектировании каталога товаров для веб-магазина.

Я делал похожую задачу в 2009 году и спроектировал такую базу данных в рамках разработки платформы для гибкой разработки веб-магазинов. Я использовал паттерн EAV (Entity — Attribute — Value).

Краткое описание реализации для SQL Server можно посмотреть здесь:
rsdn.org/forum/db/3566910.1
www.sql.ru/...​/internet-magazin#9534088

Структура полностью нормализованная, никаких полей типа JSON, XML или BLOB.

Поиск по всем фильтрам, независимо от их количества, производится одним простым запросом с использованием реляционного деления. Никаких дополнительных джойнов нет.

Но это простейший вариант, в реальности использовалось около 30 таблиц. В их число также входят таблицы для организации древовидной структура каталога и использования нескольких языков для названий товаров и их свойств (в общем случае неограниченного числа языков).

Эта структура использовалась для реализации десятков (а может и сотен) веб-магазинов, в том числе с большим числом товаров (несколько десятков тысяч) и высоконагруженных (сотни запросов в секунду). С производительностью было все в порядке.

Відповісти
Підтримати
Andrii Uhryniuk dev в myself 20.07.2020 00:10

Проблема в тому, що у одного користувача може бути як одна категорія, так і 10-20 (точно невідомо). По полям, відомо те що, у всіх категорій точно будуть одинакові поля назва, розмір, та дата. Решта користувач заповнює сам. В тому числі може в будь-який момент видалити чи додати поле, або хоч видалити усі поля, і створити нові, при цьому наявні документи не повинні видалятись. Групу фільтрів створюється так як і поля, користувач створює їх, і потім може додавати або змінювати умови у фільтрі.
Поки що вибрав зберігання значеннь полів до файлів, в локальній папці користувача, з локальною індексацією по кожному створеному фільтрі. elasticsearch на жаль, неможливо видалити окреме поле в mapping, тільки повна переіндексація документів.
А описаний Вами варіант, на даний момент реалізований. Але, чим більша кількість файлів, тим довша відповідь від БД на запит по фільтруванню.

Відповісти
Підтримати
Gregory Bocharov
Gregory Bocharov Senior Software Developer 20.07.2020 09:42

Это все прекрасно реализуется при помощи EAV.

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 20.07.2020 13:46

Якщо б фільтрування відбувалось через OR, то так, але воно відбувається через AND, і щоб виконати правильний запит мені потрібно джойнити одну і ту ж таблицю декілька разів. В цьому випадку запит може бути досить об’ємним.

Відповісти
Підтримати
Gregory Bocharov
Valeriy Shvets Engineer в Phonexa 20.07.2020 20:29

Так через OR делается — только группировку добавь по товару/папке и having count(*)=количество_фильтров.

p.s. ниже прочитал про уже начал эластик юзать, да, правильно.

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 20.07.2020 23:47

Там ще проблема з CAST’ом. Оскільки потрібно шукати по даті, по числовому значенню. Та ще й поле може містити інший обʼєкт, такі як інший файл або інший користувач.

Відповісти
Підтримати
Valeriy Shvets
Gregory Bocharov Senior Software Developer 12.09.2020 17:41

В моей схеме возможна фильтрация и по OR, и по AND, и по ним обоим вместе. Все это делается одним простым запросом.

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 12.09.2020 19:34

Там ще додались декілька вимог до пошуку. Вибрали Elasticsearch. Дякую за відповідь.

Відповісти
Підтримати
Gregory Bocharov
Alexandr Gavriluk 18.07.2020 05:01

Если задачу рассматривать с точки зрения маньяка реакционной алгебры то
PK — первичный ключ
FK — внешний ключ

Є користувачі

User
{
PK_ID
varchar name
varchar login
int64 passwordhash
}

Є користувачі і категорії, кожен користувач може бачити та додавати категорї тільки для себе.

Только для себя решается тригерами или движком (бекендом)
Category
{
PK_ID
FK_User_ID
varchar Name
}

Кожна категорія має довільні поля, які створює користувач, полів може бути до 100

CategoryField{
PK_ID
FK_Category_ID
varchar Value
}

У категорію, користувач завантажує файли, їх може бути безліч.

Files{
PK_ID
FK_Category_ID
varchar filename
blob filedata / можно ссылку на диске
}

Відповісти
Підтримати
Anton Naumov SSE в Just Techologies AS 17.07.2020 15:01

исходя из треда, я рискну предположить, что категории и поля категорий — это просто теги, которые навешены на конкретные файлы и задача стоит в том, чтобы по этим тегам файлы искать. если это так, то ваш выбор ElasticSearch.
это вообще эталонная задача для ElasticSearch — when you have objects with (flexible) fields, and you need «advanced search» functionality.

Відповісти
Підтримати
Nick Mukhin Database applications developer, DBA в Freelancer 17.07.2020 12:40

Обычно за такие консультации деньги платят.

Відповісти
Підтримати
Andrii Uhryniuk dev в myself 17.07.2020 14:01

Зрозуміло. Тоді своїми силами. Я думав це не надто важка задача, як для експертів.

Відповісти
Підтримати
Nick Mukhin
Nick Mukhin Database applications developer, DBA в Freelancer 17.07.2020 16:50

Не надто, але експерти теж їсти хочуть.

Відповісти
Підтримати
Andrii Uhryniuk
Олексій Пєніє 17.07.2020 09:16

На Мускуле сделай BLOB-поле и положи туда JSON со своим хламом. Ты ведь поиск по ним делать не будешь по этим полям среди множества пользователей?
Если есть и одинаковые для всех поля, индивидуальные — то соответственно для одинаковых пропиши поля в таблице (не забываем про индексирование), а индивидуальные сложи в BLOB.

Для файлов тупо выдели папку, соответственно на программном уровне уже их называй как тебе вздумается (используй генератор), а соответствие имени файлов делай по таблице. Расширение лучше всего сохранять исходное. Не забывай что кроме имени файлов тебе понадобится привязывать владельца и дату создания. Рано или поздно тебе этими файлами насрут, и надо будет чистить старьё. Я бы ещё добавил время последнего доступа, и уже по нему делал чистки. Обязательно сделай поле размера файла — это нужно чтобы дать квоту, чтобы тебе не засрали всё место.

По поводу фильтрации: сильно зависит от количества контента. Если у тебя по одному какому-то параметру прилетает к примеру записей не более 100 в среднем, то только по нему и надо фильтровать, остальное тупо уже отсеешь программно. Это чтобы не городить лишних полей. Например для товара в магазине это название, описание, цена, группа, производитель — под это поля выделены. А такие гадости как цвет, модель, наличие свистелки-перделки и т.п. — ты уже отфильтруешь и отсортируешь программно из полученного списка. Можешь даже на стороне клиента, если не нужен постраничный вывод (а даже если и нужен).

Почему так: компактное хранение.

Если для Украины делаешь — не забудь о двуязычии. Независимо от выбранного языка, искать будут и тем и другим. А значит нужно дублирование обеими языками, и поиск производить по обеим полям (да, по сути два поиска или UNION, иначе промах по индексу). А то и ввести скрытое поле ключевых слов, которых в названии нет, но в народе одно и то же именуется по-разному, а значит нужно каждой сущности эти данные указывать чтобы по ключевым словам нужное тоже отыскивалось.

Відповісти
Підтримати
Andrii Uhryniuk dev в myself 17.07.2020 15:40

Дякую за відповідь. Варіант непоганий. В цьому проекті, в деяких випадках я використовую JSON, для зменшення кількості запитів до БД. Але тут є ще один камінь, користувач може в будь-який момент видалити або додати поле, тоді мені прийдеться вручну в коді перебирати усі файли, та видаляти в них необхідне поле. Хоча здається, поля не будуть часто мінятись.
Напевно так і зроблю. Дякую за варіант.

Відповісти
Підтримати
Олексій Пєніє
Олексій Пєніє 20.07.2020 08:46

Ще раз: основні поля — в таблиці, весь інший хлам — в одному полі. Тому, якщо користувач щось додав у категорію, а дані вже існують — тоді це поле має ПАРАМЕТРИЗУВАТИСЬ значенням по-дефолту, яке ти будеш додавати вже на програмному рівні.

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

Я більше скажу, в полів має бути пріорітет для користувача. Ти вже на етапі, коли якісь дані заведено, будеш вирішувати — які поля важливі для пошуку, щоб видавати їх у першу чергу в списку фільтрів у формі пошуку.

Відповісти
Підтримати
Andrii Uhryniuk
Evgeniu Pozdnyakov PHP Developer в PDFFiller 17.07.2020 16:47

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

Відповісти
Підтримати
Олексій Пєніє
Олексій Пєніє 20.07.2020 08:51

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

К примеру, поиск по основным полям вернул 1500 подходящих товаров, дополнительные критерии ограничили его до 80. И дешевле на программном уровне вытянуть с базы все 1500 и уже там и отфильтровать, и сделать сравнение, и понимать вообще природу сравнения этих полей (а она может быть ну очень различной), а ещё те же по названию поля могут иметь разные единицы измерения... в общем, это всё надо писать в код. А внедрять подобный код в саму базу — весьма плохая практика.

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

Відповісти
Підтримати
Evgeniu Pozdnyakov
Andrii Uhryniuk dev в myself 20.07.2020 13:23

Так справа в тому що, кожне поле в папці може бути індексом. Тобто, є групи фільтрів, кожна група фільтрує через AND. Одна група може містити одні поля, інша поля першої групи + ще якісь. Третя група решту полів в категорії.
Я вже підключив elasticsearch для цієї справи. Дякую.

Відповісти
Підтримати
Олексій Пєніє
Олексій Пєніє 20.07.2020 17:43

Ага, залишилося лише добиватися унікальності тих полів та нормалізованого значення. Але якщо поля індивідуальні, то неймовірно висока ймовірність що той самий термін буде означати зовсім різні речі.

Відповісти
Підтримати
Andrii Uhryniuk
Konstantin Strukov 16.07.2020 21:36
Чи може перевести на NoSQL?

А как насчет гибридного варианта — не трогать реляционную модель, а для поиска сбоку прикрутить эластик?

Відповісти
Підтримати
Olena Sharovar Node.js/JS 16.07.2020 22:34

Можно переехать на postgres он реляционный но там можно делать JSON поля

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 16.07.2020 22:41

В mysql в останніх версіях, теж можна.
Тільки в моєму випадку мало допоможе.

Відповісти
Підтримати
Olena Sharovar
Olena Sharovar Node.js/JS 16.07.2020 21:16

У вас сейчас что-то вроде такого?

таблица users (id, email, ....)
таблица folders (id, name, user_id)
таблица folder_fields (folder_id, field_name, default_value)
таблица files (id, folder_id)
таблица file_fields (file_id, field_name, value)

Відповісти
Підтримати
Beaver Green 16.07.2020 19:34
Кожна категорія має довільні поля, які створює користувач, полів може бути до 100.

Одно это требование намекает на использование NoSQL.

У категорію, користувач завантажує файли, їх може бути безліч.

Про хранение файлов в БД не хочу и думать!

кожен користувач може бачити та додавати категорї тільки для себе.

А зачем тут собственно вообще единая база если у каждого пользователя свои данные и более того: своя структура этих данных?!

Відповісти
Підтримати
Andrii Uhryniuk dev в myself 16.07.2020 21:25

Ні ні, боронь Боже. Вміст файлів зберігається в структурі ФС, та віддаються через nginx, через прямий лінк.
З приводу іншої БД, я думав про різні таблиці для полів, для кожної категорії. Які б розміщались в окремій БД. Але, як зауважили нижче, це антипаттерн.

Відповісти
Підтримати
Beaver Green
Олексій Пєніє 17.07.2020 09:19

Нифига. Если есть 100 полей, это не значит что по всем ним идёт часто поиск. Обычно используемых в поиске полей мало, остальные уже применить как программную фильтрацию, если они заданы в фильтре.

То есть грубо говоря, у сущности будет полей 8, и одно поле для хранения всего остального.

Відповісти
Підтримати
Beaver Green
Beaver Green 17.07.2020 09:38
одно поле для хранения всего остального

Нарушение первой нормальной формы. Зачем использовать реляционные СУБД если нам мешают их правила? Именно для этого придумали NoSQL!

Відповісти
Підтримати
Олексій Пєніє
Dmitry Kapustin IT System Administrator 17.07.2020 11:55

Иногда денормализация очень сильно помогает. NoSQL придумали чтоб просить туда потом транзакции и превратиться в типичную реляционную СУБД. А вообще, инструмент нужно уметь использовать по назначению...

Відповісти
Підтримати
Beaver Green
Олексій Пєніє 20.07.2020 08:35

Я из того поколения, когда программирование не было религией, а канонами (разумеется нарушаемыми) было именование переменных венгерской нотацией. И знаешь, тогда всё работало в добрую сотню раз быстрее и тратило в десятки тысяч раз меньше ресурса.

В данном случае набор полей индивидуален. Им вообще не нужна нормализация. Нужно ли их структурировать — известно ТОЛЬКО на этапе запроса, но вот нужно ли их компактно хранить — ответ ДА.

NoSQL нужен чтобы это всё индексировать. Но хранить информацию об одной строчке в двух разных по природе базах — тот ещё геморой, равно как и держать только в NoSQL всё. Ты вообще хорошо осведомлён о цене ошибки в NoSQL? Во что обойдётся ошибка чтения индекса в 1 бит? Как поздно ты узнаешь, что база отравлена и все данные смешались?

Здесь же туева хуча полей, которые индексировать не нужно. Зато есть краткий набор полей, по которым ищут всегда, который характеризует сущность с позиции самого ресурса. Грубо говоря, человек который оптимизирует ресурс, который его создаёт, вообще не знает о дополнительных полях ничего — он может даже не знать о существовании конкретных категорий в будещем. Но основные данные — это чёткая задача под SQL, под скорость и удобство, под кеширование и удобную индексацию.

Суть решения — просто заниматься фильтрацией по доп-параметрам полным сканированием, притом возможно на программном уровне, а в базе контент вообще может лежать архивированным и шифрованным (весьма полезная фича, кстати).

Відповісти
Підтримати
Beaver Green
Andrey Rogovsky Допомагаю знайти першу роботу IT для Трейні та Джунів в TOPIZDATO: Соціальна IT-екосистема 16.07.2020 17:10

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

Відповісти
Підтримати
Andrii Uhryniuk dev в myself 16.07.2020 17:49

Ні. Не ігрова. Це звичайна БД як в інтернет магазинах. Тільки поля для кожної категорії можуть бути різні. Проблема тільки в фільтрах та сортуванні.
Наприклад, як себе буде поводити БД при 100 запитів в секунду, якщо в таблиці буде 1млн записів, або 50млн?
Чи не краще в цьому випадку було б розділити таблицю на декілька малих, скажімо 1000. 1 категорія = 1 таблиця.

Відповісти
Підтримати
Andrey Rogovsky
Andrey Rogovsky Допомагаю знайти першу роботу IT для Трейні та Джунів в TOPIZDATO: Соціальна IT-екосистема 16.07.2020 18:06

Если у тебя в магазине будет миллион продаж — поверь, будут деньги на решение
Так что не заморачивайся

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 16.07.2020 18:19

Просто я не знаю що буде, якщо запит буде виглядати приблизно так

SELECT f.category_id FROM fields f
LEFT JOIN fields f1 ON f.category_id=1
LEFT JOIN fields f2 ON f.category_id=1
...
LEFT JOIN fields f15 ON f.category_id=1
WHERE f.category_id=1 AND f.file_id=f1.file_id ... AND f.file_id=f15.file_id
AND CAST(f.value AS SIGNED) > 1 AND CAST(f.value AS SIGNED) < 10
AND CAST(f1.value AS DATETIME) > 1 AND CAST(f1.value AS DATETIME) < 10
... ще фільтри
AND f15.value LIKE ('%якийсь запит%') 
ORDER BY f15.value
LIMIT 0, 50
Не занадто тяжкий запит?
Відповісти
Підтримати
Andrey Rogovsky
Konstantin Strukov 16.07.2020 18:22
Не занадто тяжкий запит?

exlpain не спасет отца русской демократии? Посмотри план выполнения запроса — будет примерно понятно чего ожидать.

Відповісти
Підтримати
Andrii Uhryniuk
Dmitry Kapustin IT System Administrator 17.07.2020 12:07

explaine в json формате даст больше информации. Если мукскуль свежий, то можно и explaine analyze и tree-формат попросить...

Відповісти
Підтримати
Andrii Uhryniuk
Konstantin Strukov 16.07.2020 18:52
Я тебе советую выделить общие поля для всех категорий которые будут использованы в фильтрах и вынести их из таблицы полей в таблицу категорий, тогда все фильтры будут на одной таблице

А кто сказал что они вообще будут, эти общие поля? Судя по описанию чел пилит какой-то конструктор, то есть в предельном случае категории могут вообще не иметь ни одного общего атрибута...

Відповісти
Підтримати
anonymous
Konstantin Strukov 16.07.2020 19:03
так как самое трудоемкое будет динамично создавать эти квери в коде

Это достаточно тривиальная задача в общем-то.

Відповісти
Підтримати
anonymous
Konstantin Strukov 16.07.2020 19:13
создать джоин на потенциально 100 джоинов и 100 фильтров динамично и заранать его на муксл с норм перформансом?)))

Стоп-стоп. Речь шла исключительно о «создать квери». Если с фронта (или откуда там) прилетает модель запроса в какой-то вменяемой рекурсивной структуре (S-выражение, джсон определенной структуры, you name it) то сбилдить это в валидный селект — это грубо говоря 20 строк кода. А вот производительность полученного запроса — это отдельный большой вопрос.

Відповісти
Підтримати
anonymous
Andrii Uhryniuk dev в myself 16.07.2020 19:56

Так, все вірно. Поля створюються для окремої папки. Потім при завантаженні файлу, до кожного поля в вибраній категорії, заповнюється його значення, і прив’язується до файлу. Потім по цих полях потрібно фільтрувати та сортувати файли.
І так, це свого роду конструктор. Статичних полів там тільки 3 — ім’я, дата створення, та розмір.

Відповісти
Підтримати
anonymous
Denys Poltorak Embedded | C++ (opentowork) 16.07.2020 20:06

Навіщо це взагалі усе тягти в базу?
Чи на простіше працювати напряму з файлами, коли вони вже в наявності?

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 16.07.2020 20:19

Сам файл може бути будь-якого типу.

Відповісти
Підтримати
Denys Poltorak
Denys Poltorak Embedded | C++ (opentowork) 16.07.2020 20:31

Як його начинку тоді загнати в базу?
Чи заганяється лише назва файлу?

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 16.07.2020 20:56

Ні, тільки назва та розмір. Решту заповнює користувач.

Відповісти
Підтримати
Denys Poltorak
Andrii Uhryniuk dev в myself 16.07.2020 20:00

Проблема ще в тому що, користувач сам додає фільтри, їх може бути як 0, так і 20

Відповісти
Підтримати
anonymous
Andrii Uhryniuk dev в myself 16.07.2020 21:14

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

Відповісти
Підтримати
anonymous
Denys Poltorak Embedded | C++ (opentowork) 17.07.2020 11:17
Але з ростом кількості файлів воно почало виснути.

Що саме почало виснути? Тут може взагалі база не треба, можна робити як гіт пошуком по схованим папкам.

Відповісти
Підтримати
Andrii Uhryniuk
Andrii Uhryniuk dev в myself 17.07.2020 15:42

Браузер. Дякую за підказку. Можливо так навіть краще буде.

Відповісти
Підтримати
Denys Poltorak
Andrii Uhryniuk dev в myself 16.07.2020 19:59

Так, з клієнта приходять id раніше створених користувачем фільтрів. І проблема саме в запиті на фільтрування та сортування.

Відповісти
Підтримати
Konstantin Strukov
Олексій Пєніє 17.07.2020 09:25

Мерзость конечно!
Вообще не делай поиск по таблице с полями. Подумай, по каким полям ищут часто, и по каким сортируют. Эти поля тебе и нужно выделять отдельно и по ним индексировать. Всё остальное ты подтягиваешь ОТДЕЛЬНО, и в идеале — ОТДЕЛЬНЫМИ запросами от бэкенда.
Иначе говоря, ты вообще не спрашиваешь таблицу fields без надобности (большинство случаев), она тебе по сути нужна только чтобы называть поля правильно (как они выводятся клиенту). Больше скажу, её не грех и закешировать на уровне приложения, она у тебя килобайт 100 памяти займёт, зато не будешь сношать базу лишними запросами.

Почему так: кеширование, конечно. И оно должно быть максимально ленивыми.

Відповісти
Підтримати
Andrii Uhryniuk
Dmitry Kapustin IT System Administrator 17.07.2020 12:05

Если будешь делать «WHERE ... CAST(f.value AS SIGNED) > 1...» то можешь забыть про индексы, а LIKE «%...» убьёт всё остальное и тут тебе ни какое железо не поможет...

Відповісти
Підтримати
Andrii Uhryniuk
Михаил Кузьмин 16.07.2020 15:29

В личку напиши.. Поговорим.. Или как-то по другому..

Відповісти
Підтримати

Підписатись на коментарі

Не підписуватись
Скористайтесь акаунтом
×
з умовами використання сайту і політикою конфіденційності.