Для отображения иерархии в базе данных часто используют поле "Родитель", которое содержит ID родителя. Например, {ID, name, parent}, где parent - такое же значение как ID.

Для _хранения_ иерархии этого вполне достаточно, но возникают сложности с отображением и выборкой такой иерархии. Уж слишком сложные функции получаются просто для отображения дерева. Ведь нужно посчитать уровень элемента, а значит нужно пробежаться по родителям до корня в цикле. Для вывода дерева часто используется рекурсия, которая может свалиться в бесконечность и вызвать крах приложения и даже отладчика.

Есть и другие методы решения этих проблем, например, нарушить нормализацию и завести поле "Родители" (полный путь), содержащее коды всех родителей через слэш. Или ещё более надёжный вариант: создать отдельную таблицу с отношениями между элементами (аналог ClosureTable), которая содержит ID предка и ID потомка любой вложенности в одной записи. Это ускоряет все запросы на выборку подчинённых элементов заданного родителя, но замедляет обновление таблицы.

Что посоветуете для элегантного решения проблемы иерархии справочников в контексте разработки мобильного приложения для Android на языке Java, где базой управляет SQLite?

я так рассуждаю
1. андроид - значит мало памяти
2. оптимизация - на чтение или на запись, скорее на чтение

любые дополнительные таблицы/поля - жрут память

значит остается классический вариант с родителем

либо изменять изначальные условия 1 и 2

(1) В любом смартфоне уже 2-3 Гб есть памяти, а скоро будет 4-6. Размер базы данных конкретно для моего приложения не имеет значения. Там будут чисто цифры и тексты (учёт домашних финансов), а всё это мало места занимает (это же не картинки и не видео).

https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/0123877334

Там методы на любой вкус с анализом

(3) Ага, спасибо за ссылку. Это путь в правильном направлении.

Я бы остановился на классических nested sets

(6) Больше интересуют планы запросов и вложенные циклы. Лишь бы не получилось запросов в цикле. Самая страшная страшилка 1С-ников...

(2) тогда делай 3 таблицы
1. справочник без групп, у элемента поле родитель (ссылка на вторую таблицу)
2. рубрикатор (справочник групп)
3. связи элементов рубрикатора

простое чтение внутри группы - джойн 1+2
построение дерева на форме - 3+2 (тут возможны проблеммы при рекурсии и не однозначных связях)
запись элемента - только таб 1
перенос элемента в друг группу - 1+3
перенос группы - таб 3

(8) А не слишком ли это круто для простого иерархического справочника? Мне кажется, что даже 2 таблицы уже круто, а ты сразу 3 таблицы зарядил...

(9) зато соответствует правилам нормализации - все три таблицы не содержат неиспользуемых полей.

таблица 2. - будет вообще милипусенькая, да и таб 3 то-же не большая

(10) Можешь привести структуру таблиц для большего пониманиям этого беспредела?...

Параллельно я буду приводить свои изыскания на эту тему.

Например, типичная задача: "Выбрать все подчинённые элементы заданного родителя (с учётом вложенности)" реализуется запросом:

SELECT * FROM Expenses WHERE guid = "AA1" OR parents LIKE "%AA1%"

О, боже! Это же супер-тормоз! Да, в моём случае сработает, но что будем делать с другими случаями?

таб 1 - Name, ID_P
таб 2 - ID, Name
таб 3 - ID_1, ID_2

полное дерево джойнится
1.ID_P = 2.ID
2.ID = 3.ID_1
3.ID_1 = 3.ID_2

(13) А что за волшебная фея заполняет таб2 и таб3?

(13) + что-то наврал :)

может так

таб 1 - Name, ID_P
таб 2 - ID, Name, ID_P

1.ID_P = 2.ID
2.ID = 2.ID_P

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

храни структуру в отдеьном json-файле

Вот https://habrahabr.ru/post/46659/ про разные способы представления иерархии в реляционных базах

(18) Хорошая статья. Читаю

Временное решение пока такое:

В таблице иерархического справочника есть исходные поля:
parent - id родителя,
position - число, позиция в пределах группы,
и дополнительные поля:
folder - путь от корня до вышестоящей папки (по умолчанию "/")
level - число, глубина вложенности
ordinal - особая строка для сортировки, содержащая позиции и id всех родителей + собственную позицию и id

Пример ordinal:
/#00001#C25/#00002#AA3/#00001#BF6
где
#00001#C25 - папка с ID=C25 и позицией №1
#00002#AA3 - папка AA3 с позицией №2
#00001#BF6 - сам элемент BF6 с позицией №1

В программном коде есть добрая фея, которая заполняет все эти дополнительные реквизиты на основе parent и position. Т.е. при записи элемента справочника прилетает фея и своей волшебной палочкой перенумеровывает все элементы справочника.

Запись замедляется, но в итоге запрос вывода дерева с учётом позиции внутри группы становится тривиальным и не требует пост-обработки в программном коде:

SELECT *, folder || ID AS FullPath
FROM Catalog
ORDER BY ordinal ASC

(20)+ картинко

вот иерархия как вложенные интервалы http://www.codenet.ru/db/other/trees/

У нас уровень сразу хранится в элементе, как доп поле. Спасает неплохо.

(20) Ты изобрел клюшки?

(24) Всё уже придумано до нас

(24) Я не в курсе, что там было в клюшках.

Можно гибрид сделать.
В элементе хранить только родителя, а в группах хранить всю иерархию.

Запись ускорится, запрос ненамного замедлится

(27) Похоже на химеру

а если всю иерархию хранить в таблице вида
id элемента, id родителя, уровень
Можно одним джойном выбрать всех родителей

(30) Да, так можно. Это называется ClosureTable
https://habrahabr.ru/post/193166/

имхо самый оптимальный вариант если скорость выборки важнее скорости записи

(0) Не иметь для мобильной базы глубину иерархии более 1 уровня.

(33) У него это статьи доходов/расходов. Как по ним агрегацию делать?

(0)Однозначно две таблицы. Таблица самих строк и таблица родителей. В таком случае все запросы к скулю станут простыми + легче будет запросом/добавлять удалять строки вместе с вложеными.

(34) Можно сделать 2 справочника, подчинённых друг другу, например, группы статей и сами статьи. Тогда вообще без иерархии получится. Кстати, в одной программе так и сделано (Personal Finance от SKM)

(35) Да-да, "простыми". Например, выбрать все записи под родителем 1го уровня. А уровней, ну допустим, 2-3.

(36) Можно операции помечать не статьями, а тегами. И собирать аналитику по тегам.

Например, вы поехали куда-то с целью развлечься и купили там поесть. Тогда помечаешь расход: "путешествия", "развлечения" и "еда".

(38) Неплохая идея. И ещё надо обязательно прикрутить корованы, которые можно грабить. И котиков.

(39) сложно общую сумму по иерархии вывести

(39) а потом эта сумма будет учтена трижды :)

(42) Да нет же! Общая сумма одна. Кто сказал, что суммы по тегам надо суммировать?

+(36) Ты не понял меня. В таблице родителей прописаны все связи. Получим быстрый "обход" вверх/вниз по дереву.

(44) ой это (37).

(38) Неистово плюсую

Кстати а тег может быть вида
#ГруппаЗатрат.ЭлементЗатрат
?

(43) а как в такой структуре собрать общий расход разбитый по статьям/тегам?

(43) Это например так?
За месяц потрачено 100 рублей.
------------------
50 рублей на еду
50 на бухло
50 на развлешения

такая структура удобна для ОТБОРА, но не для ГРУППИРОВКИ

Тэги подойдут там, где нет агрегатных функций.

но ведь никто не делает отчетов:
В каких это числах я тратил деньги на развлечения?

(49) О! Родилась мысль.
Чем больше тегов к расходу, тем максимальнее окупились эти расходы.

(52) Очень здравый вопрос: когда это я ему дал в долг?

(54) долги совсем должны учитываться по другому

(55) У меня долги - это те же кошельки с типами "Взято в долг" (кредиторская) и "Выдано в долг" (дебиторская). Пока так. Не хватает ещё поля "Дедлайн", после которого пора вызывать коллекторов или телохранителей.

про nested sets было?

я даже на 1с прикручивал в РС, чтобы соединения по иерархии можно было делать

(57) было в паре статей выше

(49) Примерно. Например, повел я сына в Мак. Пишем: -630 руб. #еда #дети. А если это всё было в рамках похода в театр, то еще добавляем #развлечения.
Потому что, например, #развлечения — это не только билеты в театр, но и расходы на дорогу, на всякие мелкие игрушки, перекусы и т.д.

Вообще, конечно, структура аналитики должна идти от цели — для чего вообще вы ведете учет личных финансов.

(61) Да-да. Самое главное, получить план-факт в виде диаграммы с отображением превышений плана.

(62) а как план по тегам составлять?.
Или исходя из предыдуших преиодов? и без учета доходов?

(63) Не знаю. Теги вы придумали. У меня иерархия статей.

(61) Тогда Asmody ты скажи, какова твоя цель учета?

(65) Дожить до зарплаты :)

насколько я знаю есть 2 вида учета: реактивный и проактивный.
1 это: куда делись деньги (расходы по статьям).
2 - На эту штуку 5 руб и ни копейкой больше (бюджет)

Может еще какие варианты есть?

(67) Есть ещё режим "от получки до получки".

~Начало:
Пока ЕстьДеньги() Цикл
Тратим();
КонецЦикла;
ЗапуститьОбработчикОжиданияЗарплаты();
Перейти ~Начало:

(68) Отсутвие учета - тоже учет? )))

(69) Отсутствие учёта — тоже стиль жизни.

(63) По регистрам подобных вопросов нет?

(60) Зачем вообще теги ?
Теги - это связь одной сущности с другой.
Просто, если мы пишем статьи затрат, то у нас древовидная структура, и мы не можем одному действию записать несколько статей, нужно будет делить действие на части.
А тегов или связей наставить можно сколько угодно:
Например:МакДак->Еда,Развлечения, потом Смекта->МакДак,Лечение

(65) Понять, во что мне обходится то или иное мероприятие. Задачи "дожить до зарплаты", слава богу, нет.

(72) Ну, в общем-то, да. Это вопрос хороший — включать ли расходы на лечение в "стоимость" предприятия из-за которого пришлось лечиться.

(74) Просто, связи между расходами позволят узнать много нового и интересного.

(20) Реализовано. Работает.