Репутація українського ІТ. Пройти опитування Асоціації IT Ukraine
×Закрыть

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если задачу рассматривать с точки зрения маньяка реакционной алгебры то
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 / можно ссылку на диске
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чи може перевести на NoSQL?

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

Теж варіант. Дякую.

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

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

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

таблица 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)

В загальному так.

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

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

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

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

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

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

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

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

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

одно поле для хранения всего остального

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

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

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

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

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

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

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

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

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

Чи не краще в цьому випадку було б розділити таблицю на декілька малих, скажімо 1000. 1 категорія = 1 таблиця.

неа, это антипаттерн

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

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

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
Не занадто тяжкий запит?
Не занадто тяжкий запит?

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

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

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

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

А еще этот запрос будет иметь разное количество джоинов для каждой категории, так как колличество полей разное у категорий, а это уже прийдется конфигурировать в коде динамично

А если поля довильни — тогда и фильтры будут разные по полям для каждой категории

Я тебе советую выделить общие поля для всех категорий которые будут использованы в фильтрах и вынести их из таблицы полей в таблицу категорий, тогда все фильтры будут на одной таблице

Я тебе советую выделить общие поля для всех категорий которые будут использованы в фильтрах и вынести их из таблицы полей в таблицу категорий, тогда все фильтры будут на одной таблице

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

ну тогда дело не в квери(ях) по уже озвученым причинам, так как самое трудоемкое будет динамично создавать эти квери в коде — а значит проще всего простым квери выгрузить все в код а там уже обрабатывать, чем плодить квери на потенциально 100 джоинов по его методе

так как самое трудоемкое будет динамично создавать эти квери в коде

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

создать селект на потенциально 100 джоинов и 100 фильтров динамично и заранать его на муксл с норм перформансом?)))
по сравнению с селект * и обработкой 100 параметров в коде это действительно трудоемкая задача

создать джоин на потенциально 100 джоинов и 100 фильтров динамично и заранать его на муксл с норм перформансом?)))

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

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

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

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

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

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

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

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

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

в базе хранятся только энтити Файл и их параметры в отдельной таблице по ключу файла, которые были заполнены при загрузке файла на основании того же конфига
Загружаешь вид папки с конфига в ЮАЙ, делаешь селект * файлов где кат=Х + датасет параметров для каждого файла в виде словаря ки-валью из базы, а потом уже в коде рассовываешь в нужные места для сортировки и фильтрации, транспонировка если нужна — проще решается в коде чем 100 джоинами

и никаких 100 джоинов

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

Але з ростом кількості файлів воно почало виснути.

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

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

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

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

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

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

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

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