Иерархические данные SQL (Рекурсивный CTE против HierarchyID против таблицы закрытия)

У меня есть набор иерархических данных, которые используются в базе данных SQL Server. Данные хранятся в качестве первичного ключа в качестве ориентира, а parentGuid - как внешний ключ, указывающий на непосредственный родительский объект. Я чаще всего получаю доступ к данным через Entity Framework в проекте WebApi. Чтобы сделать ситуацию немного более сложной, мне также необходимо управлять разрешением на основе этой иерархии, так что разрешение, применяемое к родительскому объекту, применяется ко всем его потомкам. Мой вопрос таков:

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

Я могу создать Recursive CTEs, Common Table Expression (aka RCTE) для обработки иерархических данных. Это, по-видимому, самый простой подход для обычного доступа, но я беспокоюсь, что он может быть медленным при использовании для определения уровней разрешений для дочерних объектов.
Я могу создать поле типа данных hierarchyId в таблице и использовать предоставленные SQL Server функции, такие как GetAncestor(), IsDescendantOf() и т.д. Это похоже на то, что запрос будет довольно простым, но, похоже, требует довольно сложный триггер insert/update, чтобы сохранить правильное поле hierarchyId через вставки и перемещения.
Я могу создать closure table, который сохранит все отношения в таблице. Я предполагаю, что это как таковой: родительский столбец и дочерний столбец, будут представлены все родительские → дочерние отношения. (т.е. 1- > 2 2- > 3 будет представлено в базе данных как 1-2, 1-3, 2-3). Недостатком является то, что для этого требуются триггеры ввода, обновления и удаления, хотя они довольно просты, и этот метод генерирует много записей.

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

PS Я также открыт для любых альтернативных решений этой проблемы

Ответ 1

Я использовал все три метода. Это в основном вопрос вкуса.

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

Таблица закрытия больше похожа на дополнение к вышесказанному. Поиск всех потомков данного node является молниеносно, вам не нужны CTE, просто одно дополнительное соединение, поэтому оно сладкое. Да, количество записей взрывается, но я думаю, что это не более чем N-1 раз число узлов для дерева с глубиной N (например, третичное дерево глубины 5 потребует 1 + 3 + 9 + 27 + 81 = 121 соединений при сохранении только отношения родитель-потомок против 1 + 3 + (9 * 2) + (27 * 3) + (81 * 4) = 427 для таблицы закрытия). Кроме того, записи таблицы закрытия настолько узки (как минимум, 2 интервала), что они занимают почти не место. Создание списка записей для вставки в таблицу закрытия при вставке новой записи в иерархию занимает небольшую часть служебных данных.

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

http://sqlblogcasts.com/blogs/simons/archive/2008/03/31/SQL-Server-2008---HierarchyId---How-do-you-move-nodes-subtrees-around.aspx

Основной недостаток, который я нашел в иерархии, - это кривая обучения. Это не так очевидно, как работать с ним, как с двумя другими методами. Я работал с некоторыми очень яркими разработчиками SQL, которые часто зацепились за него, поэтому у вас есть один или два резидентных эксперта, которым приходится задавать вопросы всем остальным.