Как лучше всего реализовать настраиваемую древовидную структуру данных (то есть древовидную структуру с неизвестным числом уровней) в базе данных?
Я сделал это один раз, прежде чем использовать таблицу с внешним ключом для себя.
Какие еще реализации вы могли увидеть, и имеет ли смысл эта реализация?
Вы упомянули наиболее часто применяемый список смежности: https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets
Есть и другие модели, включая материализованный путь и вложенные наборы: http://communities.bmc.com/communities/docs/DOC-9902.
Джо Селко написал книгу по этой теме, которая является хорошей справочной информацией с точки зрения общего SQL (она упоминается в приведенной выше ссылке на вложенный набор статей).
Кроме того, Ицик Бен-Ганн дает хороший обзор наиболее распространенных опций в своей книге «Внутри Microsoft SQL Server 2005: запросы T-SQL».
При выборе модели следует учитывать следующие основные моменты:
1) Частота изменения структуры - как часто меняется фактическая структура дерева. Некоторые модели обеспечивают лучшие характеристики обновления структуры. Однако важно отделить изменения структуры от других изменений данных. Например, вы можете смоделировать организационную структуру компании. Некоторые люди будут моделировать это как список смежности, используя идентификатор сотрудника, чтобы связать сотрудника с его руководителем. Обычно это неоптимальный подход. Подход, который часто работает лучше, - это моделирование организационной структуры отдельно от самих сотрудников и сохранение сотрудника как атрибута структуры. Таким образом, когда сотрудник покидает компанию, сама организационная структура не нуждается в изменениях, а только в ассоциации с ушедшим сотрудником.
2) Является ли дерево тяжелым для записи или чтения - некоторые структуры работают очень хорошо при чтении структуры, но несут дополнительные накладные расходы при записи в структуру.
3) Какие типы информации вам нужно получить от структуры - некоторые структуры лучше всего предоставляют определенные виды информации о структуре. Примеры включают поиск узла и всех его дочерних узлов, поиск узла и всех его родителей, определение количества дочерних узлов, удовлетворяющих определенным условиям и т. Д. Вам необходимо знать, какая информация потребуется от структуры, чтобы определить структуру, которая лучше всего подходит твои нужды.
Взгляните на Управление иерархическими данными в MySQL. В нем обсуждаются два подхода к хранению и управлению иерархическими (древовидными) данными в реляционной базе данных.
Первый подход - это модель списка смежности, которую вы, по сути, описываете: наличие внешнего ключа, который ссылается на саму таблицу. Хотя этот подход прост, он может быть очень неэффективным для определенных запросов, таких как построение всего дерева.
Второй подход, обсуждаемый в статье, - это модель вложенных множеств. Этот подход гораздо более эффективен и гибок. См. Статью для подробного объяснения и примеров запросов.
Если вам необходимо использовать реляционную базу данных для организации древовидной структуры данных, Postgresql имеет классный модуль ltree, который предоставляет тип данных для представления меток данных, хранящихся в иерархической древовидной структуре. Вы можете получить эту идею оттуда. (Для получения дополнительной информации см .: http://www.postgresql.org/docs/9.0/static/ltree.html)
Обычно LDAP используется для организации записей в иерархической структуре.
Для меня имеет смысл иметь таблицу с внешним ключом к самой себе.
Затем вы можете использовать обычное табличное выражение в SQL или предыдущий оператор подключения в Oracle для построения своего дерева.
Если кто-то, использующий MS SQL Server 2008 и выше, столкнется с этим вопросом: SQL Server 2008 и выше имеет новую функцию "hierarchyId", разработанную специально для этой задачи.
Дополнительная информация на https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server
