У меня есть приложение, которое требует «простого» генеалогического древа. Я хотел бы иметь возможность выполнять запросы, которые дадут мне данные для всей семьи, учитывая один идентификатор от члена в семье. Я говорю просто, потому что не нужно учитывать усыновление или любые другие неясности. Требования к заявке следующие:
У меня возникли проблемы с правильной структурой базы данных. Пока я придумал два решения, но они не очень надежны и, вероятно, скоро выйдут из-под контроля.
Решение 1 включает размещение поля family_ids в таблице people и сохранение списка уникальных идентификаторов семейства. Каждый раз, когда двое людей размножаются, списки проверяются друг против друга, чтобы убедиться, что никакие идентификаторы не совпадают, и если все проверит, объединит два списка и установит это как поле family_ids ребенка.
Пример:
Father (family_ids: (null)) breeds with Mother (family_ids: (213, 519)) -> Child (family_ids: (213, 519)) breeds with Random Person (family_ids: (813, 712, 122, 767)) -> Grandchild (family_ids: (213, 519, 813, 712, 122, 767)) И так далее и т. Д. Проблема, с которой я вижу, – это то, что списки становятся необоснованно большими с течением времени.
Решение 2 использует ассоциации cakephp для объявления:
public $belongsTo = array( 'Father' => array( 'className' => 'User', 'foreignKey' => 'father_id' ), 'Mother' => array( 'className' => 'User', 'foreignKey' => 'mother_id' ) );
Теперь установка рекурсивного на 2 даст результаты матери и отца вместе с их матерью и отцом и т. Д. И т. Д. Вплоть до конца. Проблема с этим маршрутом заключается в том, что данные находятся в вложенных массивах, и я не уверен, как эффективно работать с кодом.
Если кто-то сможет меня направить в направлении наиболее эффективного способа справиться с тем, что я хочу достичь, это будет чрезвычайно полезно. Мне очень благодарна всяческая помощь, и я с удовольствием отвечу на любые вопросы, которые есть у кого-либо. Большое спасибо.
В SQL (вернее, RDBS) я бы использовал следующее решение:
1) создать таблицу people со следующими полями: id , name , father_id , mother_id . Первый является типичным столбцом первичного ключа, father_id и mother_id относятся к этому столбцу, но являются NULLable (чтобы добавить новые семейные строки).
2) создать relatives таблицы со следующими полями – person_id , ancestor_id . Оба не являются NULL, оба образуют составной первичный ключ, оба также являются FK для person.id .
Вот и все. Нет, действительно! ) Теперь рассмотрим ваши задачи:
Это тоже довольно выполнимо: INSERT INTO people (name) VALUES ('some_name') . Хитрость заключается в том, чтобы сделать другую вставку, связанную с этим новым человеком, родственниками: INSERT INTO relatives VALUES (%new_person_id%, %new_person_id%)
Что то, что для? Рассмотрим наиболее распространенную задачу: добавьте человека, у которого на самом деле уже есть как отец, так и мать, перечисленные в ваших таблицах. С этой структурой это делается так же просто, как (после вставки соответствующей записи в people и получения этого person_id в результате) …
INSERT INTO relatives SELECT %new_person_id%, ancestor_id FROM relatives WHERE person_id IN (%father_id%, %mother_id%); INSERT INTO relatives VALUES (%new_person_id%, %new_person_id%);
С описанной выше структурой это довольно просто: вам нужно искать две записи у relatives которые имеют такое же значение в поле ancestor_id . Например:
SELECT COUNT(*) FROM relatives ra INNER JOIN relatives rb ON ra.ancestor_id = rb.ancestor_id WHERE ra.person_id = %person_a_id% AND rb.person_id = %person_b_id%
В этой структуре довольно легко искать всех предков и детей; но я бы предпочел использовать ненормализованный подход (т. е. хранить в папке father_id и mother_id в первой таблице), чтобы ускорить поиск прямых родителей / детей – на самом деле это можно сделать только с первой таблицей.
Вот пример работы (хотя и немного короткий) SQL Fiddle, чтобы показать это более практичным цветом. )