Комментарии 35
Историю изменений не планируется хранить?
Стоило накидать пару тройку селектов, чтоб показать удобство архитектуры, тогда уж
селекты то как раз удобней не стали :) но и не сильно усложнились, в следующей статье будут примеры использования с вычислением параметров поиска и с собственно поиском, плюс выдача Значений Характеристик для конкретной Сущности, остальные вещи вроде тривиальные, короче будут примеры, куда же без них.
Чуть сложнее, чем стандартная Entity-Attribute-Value модель. Сам подобным «грешил» в проекте, представляющим собой набор произвольных справочников. подтверждаю, что разработка бизнес-логики с таким подходом упрощается, кода совсем немного получается, и он слабо связан. Хочу заметить, что для аналитики этих данных их лучше денормализовать (мы сбрасывали денормализованные данные в MongoDB).
title — наименование (name пришлось заменить на title, потому что name это ключевое слово для PostgreSql)
Использую PostgreSQL с 1999 года.
Поле с названием name встречается в каждой базе данных.
CREATE TABLE mail (
name character varying,
id integer NOT NULL
);
(пример из какого-то актуального бэкапа)
Что я делаю не так?
У вас для реализации дерева используется adjacency list. А если понадобятся сложные выборки, придётся писать много-много join-ов?
Сложные выборки это всегда много много джоинов, не понимаю сути вопроса.
Один иерархический запрос для того что бы получить всех потомков или наоборот что бы получить всех родителей.
Один иерархический запрос для того что бы получить всех потомков или наоборот что бы получить всех родителей.
is_hidden INTEGER DEFAULT 0,
Чем продиктован выбор типа для этого поля? Почему не boolean?
Ну и кажется логичным добавить NOT NULL.
element_tree_id — ссылка на родительский элемент дерева.
Если не знать, то никогда не догадаешься, что под таким именем скрывается ссылка на родительский элемент.
Обычно это поле называют parent_id.
is_hidden INTEGER DEFAULT 0,
выбор продиктован тем что не во всех СУБД возможно создать колонку с типом boolean, продиктован тем что для простоты миграции от СУБД к СУБД, типов данных всего четыре:
CHAR(100)
VARCHAR(4000)
INTEGER
TIMESTAMP
и ещё парочка вспомогательных это DOUBLE PRECISION и INTERVAL.
причем без CHAR(100) можно обойтись, это просто дань памяти очень старым СУБД, из тех времён когда BTREE было в диковинку.
Нет ограничения NOT NULL по моим философским соображениям — не надо себя ограничивать без острой необходимости.
element_tree_id — ссылка на родительский элемент дерева.
есть такая нотация и она в PostgreSql продвигается самими разработчиками:
<имя_таблицы_с_индексом>_<колонка_индекса>
если знать это правило то становиться очевидным, что эта колонка ссылается на таблицу element_tree на колонку id, не всегда удаётся следовать этому правилу и приходиться делать исключения, но здесь всё гармонично получается.
У меня легкий когнитивный диссонанс:
выбор продиктован тем что не во всех СУБД возможно ...
IMHO, несколько в противоречии с
есть такая нотация и она в PostgreSql продвигается
Я не совсем понял, мы привязываемся к Postgres'у или пытаемся быть универсалами? Я, кстати, также ожидал увидеть "parent_id" в "element_tree".
Исходя из моего опыта работы с EAV структурой в Magento я несколько опасаюсь увидеть ваш SQL для постраничной выборки данных с фильтрацией и сортировкой (типовой use case). Но тем интереснее будет взглянуть на в следующей статье. Удачи.
name convension это name convension, а выбор типов данных для совместимости это выбор типов данных.
Для выборки данных позиции селект с 4-6 джоинами и во фразе WHERE куча условий is_hidden = 0
Для поиска делается отбор по каждому свойству и затем находиться пересечение множеств (INTERSECT на стороне СУБД).
Для вычисления параметров поиска выполняется или max()/min() или group by.
Построение структуры — банальным иерархическим запросом.
Куча джоинов это занудно конечно, но в целом достаточно прозрачная логика.
Фишка в универсальности, надо платить джоинами? пусть.
ваш SQL
Для выборки данных позиции селект с 4-6 джоинами и во фразе WHERE куча условий is_hidden = 0
Для поиска делается отбор по каждому свойству и затем находиться пересечение множеств (INTERSECT на стороне СУБД).
Для вычисления параметров поиска выполняется или max()/min() или group by.
Построение структуры — банальным иерархическим запросом.
Куча джоинов это занудно конечно, но в целом достаточно прозрачная логика.
Фишка в универсальности, надо платить джоинами? пусть.
Куча джойнов — это аццки медленно на большом количестве данных.
Несколько не ожидал увидеть мой SQL именно в таком, не совсем классическом виде, но мое любопытство удовлетворено в полной мере. Спасибо.
Для поиска делается отбор по каждому свойству и затем находиться пересечение множеств (INTERSECT на стороне СУБД).
А как быть с INTERSECT в MySQL?
в смысле? MySQL умеет делать INTERSECT, или я заблуждаюсь?
что делать с иерархическими запросами в MySQL вот в чём вопрос, но на него тут в коментах уже были ответы:
Nested Sets
closure table
adjacency list
что делать с иерархическими запросами в MySQL вот в чём вопрос, но на него тут в коментах уже были ответы:
Nested Sets
closure table
adjacency list
Нет, MySQL не имеет синтаксиса INTERSECT, к сожалению, везде только предложения симулировать его.
https://www.techonthenet.com/mysql/intersect.php
Вопрос, скорее, к тому, что, как уже спрашивали выше — непонятно, предназначено ли решение для разных СУБД, или конкретно под какую-то определенную.
https://www.techonthenet.com/mysql/intersect.php
Вопрос, скорее, к тому, что, как уже спрашивали выше — непонятно, предназначено ли решение для разных СУБД, или конкретно под какую-то определенную.
понятно. можно симулировать через
вот такая симуляция пересечения множеств, для простоты в подзапросах используется одна таблица, но на деле там будет выборка из нескольких.
вычислять перечисление умеют MS SQL, ORACLE SQL, PostgreSql, IBM DB2, я думаю любая «старая» СУБД умеет.
Надо заметить что MS SQL и ORACLE SQL бесплатны для баз размером до 10 гигов, этого за глаза хватит для многих проектов.
MySql он же для другого придуман, его цель быстро отдать данные, работа с данными это не про MySql, в связке с MySql должен работать сервер приложений, вот он пусть и вычисляет пересечение множеств и прочие радости типа EXCLUDE.
SELECT *
FROM
( select id from data_table where string_data LIKE '%pattern%') AS T1
JOIN ( select id from data_table where integer_data > 0 ) AS T2
ON T1.id = T2.id
вот такая симуляция пересечения множеств, для простоты в подзапросах используется одна таблица, но на деле там будет выборка из нескольких.
вычислять перечисление умеют MS SQL, ORACLE SQL, PostgreSql, IBM DB2, я думаю любая «старая» СУБД умеет.
Надо заметить что MS SQL и ORACLE SQL бесплатны для баз размером до 10 гигов, этого за глаза хватит для многих проектов.
MySql он же для другого придуман, его цель быстро отдать данные, работа с данными это не про MySql, в связке с MySql должен работать сервер приложений, вот он пусть и вычисляет пересечение множеств и прочие радости типа EXCLUDE.
НЛО прилетело и опубликовало эту надпись здесь
А как этот подход по скорости?
НЛО прилетело и опубликовало эту надпись здесь
Если выборок больше, чем модификаций, а обычно так и есть, то работает лучше, чем предложенный вариант с рекурсией.
предложенный вариант без рекурсии, выборку узлов выполняет СУБД, приложение получает готовый набор данных.
В приведенном примере дерево в реляционке храниться как AL со ссылкой родительский узел. И тут вариантов нет, либо на стороне приложения, либо на стороне СУБД выборка поддерева делается ресурсией. Причем в данной реализации как я понимаю в приложении мы не получаем все поддерево (детей, внуков и дальше вглубь) узла одним запросом (в отличие от того же NS).
Причем в данной реализации как я понимаю в приложении мы не получаем все поддерево
данная не реализация а техническая идея, при желании можно получить дерево целиком, для карты рубрик каталога например, на практике нет необходимости в получении всего дерева, максимум на два уровня ниже от текущего или что бы построить путь от потомка до корня.
На счёт рекурсии не факт, что СУБД делает рекурсию, есть алгоритмы для построения дерева без рекурсии, кроме того СУБД ловит ошибки структуры дерева когда отдельные куски могут быть замкнуты сами на себя.
Варианты есть. Особенно если использовать PostgreSQL. Правда, одним только id родителя, конечно не обойтись. Но, если слегка (или не слегка — а как удобнее) денормализовать таблицу, дописать пару сишных функций и добавить на их основе Operator CLASS, можно вполне непринужденно получать любое количество упорядоченных потомков одним индексируемым запросом безо всякой рекурсии. Вобщем-то можно обойтись и без дополнительных функций — использовав преобразование к бинарным строкам — но там возникает несколько засад с экранированиями спецсимволов, посему проще все-таки с функциями. Побочным эффектом денормализации будет необходимость апдейтить потомков при перемещении или копировании узла. Но в любом случае получается значительно экономичнее, чем nested sets, при сохранении максимальных скоростей выборки.
Впрочем, адекватно такую схему можно сделать на PostgreSQL и на Oracle (возможно получится на MS SQL, или еще на чем-нибудь «толстом» — но тут я просто не в теме). То есть, универсальность в широком смысле теряется.
Впрочем, адекватно такую схему можно сделать на PostgreSQL и на Oracle (возможно получится на MS SQL, или еще на чем-нибудь «толстом» — но тут я просто не в теме). То есть, универсальность в широком смысле теряется.
Имелась в виду рекурсия в виде многочисленных join-ов таблицы на саму себя.
НЛО прилетело и опубликовало эту надпись здесь
Хочу попросить автора такое количество таблиц иллюстрировать ER-диаграммой. Далеко не у всех в планы входит сразу бросаться это реализовывать, а вот понять и простить прочувствовать прямо во время чтения — у многих.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Идеальный каталог, набросок архитектуры