#sql #sql_server
Есть единица дерева. У нее есть father и mother. Не могу придумать как оптимально
хранить и использовать родственные связи. Типа того
Ребенок - тот, кому текущая единица является мамой или папой (самое сложное, на мой
взгляд в плане хранения)
Внук - ребенок ребенка
брат/сестра - другой ребенок мамы и папы
сводные брат/сестра - другой ребенок мамы или папы
Бабушка/дедушка - мама/папа мамы/папы
Дядя/тетя - брат/сестра мамы/папы
Племянник - ребенок брата/сестры
Ну и так далее.
То есть в результате я хочу получить такую схему данных, при которой я могу:
Максимально легко добавлять в справочник родственных связей новые элементы
Максимально легко (желательно даже одним запросом) получить для текущей единицы дерева
получить нужных родственников
Подскажите хотя бы схему таблицы родства. Я начну
ID/Наименование родства/......
база mssql
UPD:
Почитал я комментарии и увидел, что не все и не полностью меня поняли. Попробую сказать
другим языком. Я хочу, чтобы, когда я решу добавить новую степень родства (например,
двоюродные братья), мне не пришлось бы перекомпилировать программу, а вместо этого
я бы просто добавил в базу информацию, типа ("код", "двоюродный брат", "сын сестры/брата
матери"). Вопрос только в том, как эти данные лучше всего хранить, чтобы было удобно
их добавлять, читать, использовать.
Ответы
Ответ 1
Нам нужны следующие таблицы: Люди с указанием их непосредственных отца и матери: create table people( id int identity not null, -- ID человека fio varchar(200) not null, -- ФИО father int, -- ID отца mother int, -- ID матери half int, -- Жена/Муж (текущие, если нужны прошлые - усложняйте структуру), sex tinyint, -- Пол: 1-Женщина, 2-Мужчина constraint pk_people primary key (id) ); Родственные отношения: create table relRule( rid int identity not null, -- ID отношения direct tinyint not null, -- Направление шага: 1-Родители, 2-Дети, 3-Братья, 4-Супруг mask tinyint not null default(3), -- Маска ветви: 1-Женская, 2-Мужская, 3-Любая xmask tinyint not null default(3),-- Маска исключительности: 1-Сводные, 2-Родные, 3-Все (родные и сводные) -- Имеет значение только для движения "вбок" (сестры/братья) prev_rid int null, -- Предыдущее отношение в дереве wname varchar(64) null, -- Родственник женщина (название) mname varchar(64) null, -- Родственник мужчина (название) comment varchar(64) null, -- Комментарий constraint pk_relRule primary key (rid) ); Дерево родственных отношений строится в виде ... дерева. На вершине лежат записи с prev_rid=NULL - ближайшие родственники. Любая ветвь может вести в одном из четырех направлений: к родителям, к детям, к братьям, к супругу. На ветви хранится название для родственника найденного по ней, мужчины и женщины, отдельно. Если через данную ветвь далее надо идти только по мужской или женской ветви, то такая ветвь разделяется на две, с указанием в поле mask пола родственника. При этом в названии родственника имеет смысл заполнять только одно из полей (соответственно полу записи). Например жена и муж делаются отдельными ветвями, потому как их родственники зовутся совершенно по разному (свекр, свекровь, теща, тесть). В сложных случаях могут появляться служебные ветви без названий родственников на них, служащие только что бы добраться до следующих узлов, в этом случае рекомендуется в поле comment указывать назначение ветви. Для движения по дереву к братьям и сестрам есть дополнительный признак "Исключительность" (xmask), он определяет оба родителя должно совпадать или только один. Для отделения родных и сводных. Пример дерева родственных отношений: direct=Родители(1), mask=Оба(3) = Мать/Отец direct=Братья(3), mask=Оба(3), xmask=Родные(2) | = Тетя/Дядя direct=Дети(2),mask=Оба(3) | | = Двоюродные Сестра/Брат direct=Родители(1), mask=Оба(3) | = Бабушка/Дедушка direct=Супруг(4), mask=Мужчина(2) = Муж direct=Родители(1), mask=Оба(3) = Свекровь/Свекр direct=Братья(3), mask=Оба(3) = Золовка/Деверь А вот запрос, который по этой структуре ищет родственников: with Q(rel_name,P,id,fio,father,mother,half,sex,rid,mask,xmask) as ( select Cast(NULL as varchar(64)),0 P,P1.*,0 rid,Cast(0 as tinyint) mask,Cast(3 as tinyint) xmask from people P1 where P1.id=13 -- <<-- Для кого ищем родственников union all select * from -- <<-- Рекурсивная часть запроса ( select choose(P2.sex,R.wname,R.mname) rel_name, case when P1.father=P2.father and P1.mother=P2.mother then 2 else 1 end | case R.direct when 3 then 0 else 3 end P, P2.*,R.rid,R.mask,R.xmask from Q,people P1,people P2,relRule R where P1.id=Q.id and ((R.prev_rid is null and Q.rid=0) or R.prev_rid=Q.rid) and ( (R.direct=1 and P2.id in(P1.father,P1.mother)) or(R.direct=2 and (P2.father=P1.id or P2.mother=P1.id)) or(R.direct=3 and (P1.father=P2.father or P1.mother=P2.mother)) or(R.direct=4 and P2.id=P1.half) ) and P1.id!=P2.id and R.mask & P2.sex>0 -- Проверка пола ветви ) A where A.P & A.xmask > 0 -- Проверка Родной/Сводный ) select Q.* from Q where rel_name is not null SQLFIDDLE Примером выборки степеней родства. Тут же представлен большой пример дерева родства (пришлось транслит сделать, а то fiddle не выводил русский :( )Ответ 2
Можно попробовать следующий подход (опишу схематично). Находясь в каком-то узле дерева (а скорее графа) нужно обойти все связанные с ним узлы (а также узлы связанные с ними, и т.д.), двигаясь по осям parent (родители), child (дети) и spouse (супруг/супруга). Для удобства можно выделить ось sibling (родные братья/сёстры) как отдельную. В результате обхода сформировать (быть может даже средствами SQL) XML-сущность, к примеру, такого вида:Основная сложность будет именно в том, чтобы сделать этот обход, не зацикливаясь, не углубляясь чрезмерно из-за рекурсии и не создавая слишком много ненужных копий узлов, т.е. сформировать XML с достаточной и в то же время не слишком избыточной информацией. Предположим мы это сделали. А далее, тип родственной связи - это определённое XPath выражение, взяв которое, следует выполнить XQuery запрос к сформированной XML-сущности, например: Родители: /*[1]/parent Мама: /*[1]/parent[@gender='F'] Бабушки и дедушки: /*[1]/parent/parent Дедушки: /*[1]/parent/parent[@gender='M'] Пра-, пра-пра, пра-пра-пра и т.д. : /*[1]/parent/parent//parent Дяди: /*[1]/parent/sibling[@gender='M'] Двоюродные братья/сёстры: /*[1]/parent/sibling/child Тесть: /*[1]/spouse/parent[@gender='M'] Братья: /*[1]/sibling[@gender='M'] Дети: /*[1]/child Сыновья: /*[1]/child[@gender='M'] Племянницы: /*[1]/sibling/child[@gender='F'] Внуки и внучки: /*[1]/child/child и т.п. Что касается структур хранения, то, думаю, я бы хранил примерно так (упрощённо). Узлы графа - люди. create table People ( ID int, Name nvarchar(200), Gender char(1) ) Рёбра графа - ближайшие родственные связи (храним только такие: родители, дети, братья/сёстры, супруг/супруга, остальные будут вычисляться). create table Relations ( PersonID int, RelativeID int, RelationType tinyint -- 1 - parent, 2 - child, 3 - spouse, 4 - sibling ) Ответ 3
Спасибо за помощь, но я не воспользуюсь ни одним из вариантов. Мне пришла в голову другая идея. Может, она будет кому либо интересна. Итак, есть таблица с деревом id / fatherId / motherId точно такую же таблицу (в плане схемы дерева) создаю еще раз, но уже для родственных связей id / fatherId / motherId / Value (это название родства) / gender заполнена она будет примерно так some_id / father_id / mother_id / self / null father_id / Null / null / отец / null mother_id / null / null / мать / null brother_id / father_id / mother_id / брат / male sister_id / father_id / mother_id / сестра / female А теперь вот в чем фокус. В таблице с деревом есть начальный ИД, который выбирается при инициализации, который обозначает человека, вокруг которого строится дерево. В таблице родства он соответствует строке с Value = 'self' И когда я разворачиваю дерево в любую сторону (то есть делаю запрос к таблице с деревом), точно такой же фильтр я применяю и для таблицы с родством с учетом пола (или gender = null, если пол не имеет значения). Как то так. Еще не осуществил, но должно получиться :) Если запрос к таблице с деревьями выглядит так SELECT * FROM Trees WHERE id=@root_fatherId то запрос к таблице родства будет выглядеть так SELECT * FROM FamilyTies WHERE id=@self_fatherId AND id!=@self AND gender=@finded_gender А можно вообще вьюху замутить
Комментариев нет:
Отправить комментарий