There is a tree unit. She has a father and a mother. I can not figure out how to optimally store and use family ties. Something like that

  • A child is one to whom the current unit is mom or dad (the most difficult, in my opinion, in terms of storage)
  • Grandson - child baby
  • brother / sister is another child of mom and dad
  • stepbrother - another mom or dad child
  • Grandmother / grandfather - mom / dad mom / dad
  • Uncle / aunt - brother / sister mom / dad
  • Nephew - child brother / sister

Well, and so on.

That is, as a result, I want to get a data scheme in which I can:

  • As easy as possible to add new items to the sibling directory.
  • It is as easy as possible (preferably with a single query) to get the right relatives for the current unit of the tree.

Prompt at least the scheme of the relationship table. I will begin

ID / Name of relationship / ......

base mssql

UPD:

I read the comments and saw that not all and not completely understood me. I will try to speak in another language. I want, when I decide to add a new degree of relationship (for example, cousins), I would not have to recompile the program, but instead I would just add information to the database, such as (code, cousin, son sisters / brothers mother "). The only question is how this data is best stored, to be convenient to add, read, use.

  • two tables can be made: [people] (id, name, bdate) and [relations] (id, peopleId1, peopleId2, typeRelations, dateChanges) - Stack
  • @iRumba Of course you only need to keep close relatives. Those. for good we only need the child to keep two parents. All other relatives are calculated. Store for example great-grandfather clearly makes no sense. because when you add one person, you will have to run across the entire network and write to him all the relatives. Here's how to describe the "degree of kinship", so that you can quickly find out who you need and not to write a piece of code for each type of relationship - this is a really interesting question - Mike
  • @iRumba It is interesting, but in addition to the "father" and "mother" there may be close relatives, in the case of IVF or surrogate motherhood. Those. whether the “child” can have a “biological mother” and a “surrogate” and other exotic cases. And if you look at Europe ... so there the situation can be terribly confusing ... - Mike
  • @Mike "This is how to describe the" relationship degree "," - hierarchyId? “the situation can be terribly confusing at all” - yes, it seems to me that I cannot do without a separate table [relation]. - Stack
  • @Stack I want the relationship table ... but not sure if we see its scheme and purpose as the same - iRumba

3 answers 3

We need the following tables:

People indicating their immediate father and mother:

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) );

Relationships :

 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) );

The tree of relationships is built in the form of ... a tree. At the top are the entries with prev_rid=NULL - the closest relatives. Any branch can lead in one of four directions: to parents, to children, to brothers, to a spouse. The name of the relative found for it, a man and a woman, is stored on the branch separately. If through this branch one has to go only along the male or female branch, then such a branch is divided into two, indicating in the field mask gender of the relative. In this case, in the name of a relative, it makes sense to fill only one of the fields (according to the floor of the record). For example, a wife and a husband are made separate branches, because their relatives are called completely differently (father-in-law, mother-in-law, mother-in-law, father-in-law). In difficult cases, service branches may appear without names of relatives on them, employees just getting to the next nodes, in this case it is recommended to indicate the purpose of the branch in the comment field.

To move on a tree to brothers and sisters there is an additional sign "Exclusivity" ( xmask ), it defines both parents must be the same or only one. For the separation of relatives and consolidated.

Example of a relationship tree:

 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) = Золовка/Деверь

But the query that is looking for relatives in this structure:

 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 AP & A.xmask > 0 -- Проверка Родной/Сводный ) select Q.* from Q where rel_name is not null

SQLFIDDLE An example of a sample of degrees of relationship. A large example of a kinship tree is also presented here (I had to translate it, otherwise fiddle did not display Russian :()

  • Not bad. Although I would get rid of the mask of exclusivity and instead increased the table in height by adding descriptions for each type (cousins, second cousins, mother’s summary, father’s summary, etc.) - iRumba
  • @iRumba And how the increase in height will determine that the brother is consolidated. We took a maternal brother, but we still need to check who his father is ... "Exclusivity" just solves this problem, verifies the second parent. - Mike
  • To do this, you can use father, mother and sex. Exclusivity did not suit me that not all countries have such a division. In some places, for example, there is no division into summary ones. And something where there is no division into cousins ​​and second cousins. Do not create my own table for each country - iRumba
  • Well, the presence of a column in the table still did not bother anyone. If it is not necessary to divide for a specific model, it means there is 3 in all records and that's it. It's just a sign, you have to or don’t do a father, mother check. No matter how you check it depending on the country, this sign will still be needed and it is there! - Mike

You can try the following approach (I will describe schematically).

Being in some node of the tree (or rather a graph) you need to go around all the nodes connected with it (as well as the nodes associated with them, etc.), moving along the parent (parents), child (springs) and spouse (spouse /spouse). For convenience, the sibling axis (siblings) can be singled out as a separate one. As a result of a crawl, form (perhaps even by SQL means) an XML entity, for example, of the following type:

 <person name="Я" gender="M"> <parent name="Папа" gender="M"> <parent name="Дед по линии отца" gender="M" /> <parent name="Бабушка по линии отца" gender="F" /> <sibling name="Дядя по линии отца" gender="M"> <child name="Двоюродная сестра по линии отца" gender="F" /> </sibling> <sibling name="Тётя по линии отца" gender="F" /> </parent> <parent name="Мама" gender="F"> <parent name="Дед по линии матери" gender="M" /> <parent name="Бабушка по линии матери" gender="F" /> </parent> <spouse name="Жена" gender="F"> <parent name="Тесть" gender="M" /> <parent name="Тёща" gender="F"> <parent name="Бабушка по линии матери" gender="F" /> </parent> <sibling name="Сестра жены" gender="F" /> </spouse> <child name="Дочь" gender="F"> <child name="Внучка" gender="F" /> </child> <child name="Сын" gender="M"> <child name="Внук" gender="M" /> </child> <sibling name="Брат" gender="M"> <child name="Племянница" gender="F" /> </sibling> <sibling name="Сестра" gender="F"> <child name="Племянник" gender="M" /> </sibling> </person>

The main difficulty will be precisely to make this detour without getting hung up, not going deep because of recursion and not creating too many unnecessary copies of the nodes, i.e. generate XML with sufficient and at the same time not too redundant information. Suppose we did it.

And further, the type of relationship is a certain XPath expression, taking which, you should execute an XQuery query to the generated XML entity, for example:

  • Parents: /*[1]/parent
  • Mom: /*[1]/parent[@gender='F']
  • Grandparents: /*[1]/parent/parent
  • Grandfathers: /*[1]/parent/parent[@gender='M']
  • Pra-, pra-pra, pra-pra-pra, etc. : /*[1]/parent/parent//parent
  • Uncles: /*[1]/parent/sibling[@gender='M']
  • Cousins ​​/ sisters /*[1]/parent/sibling/child
  • Test: /*[1]/spouse/parent[@gender='M']
  • Brothers: /*[1]/sibling[@gender='M']
  • Children: /*[1]/child
  • Sons: /*[1]/child[@gender='M']
  • Nieces: /*[1]/sibling/child[@gender='F']
  • Grandchildren and Granddaughters: /*[1]/child/child

etc.

As for storage structures, I think I would keep it this way (in a simplified way).

The nodes of the graph are people.

 create table People ( ID int, Name nvarchar(200), Gender char(1) )

The edges of the graph are the nearest family ties (we store only these: parents, children, brothers / sisters, spouse, the rest will be calculated).

 create table Relations ( PersonID int, RelativeID int, RelationType tinyint -- 1 - parent, 2 - child, 3 - spouse, 4 - sibling )
  • And how to get a stepbrother? - Mike
  • @Mike, I propose to put the summary ones in /*[1]/parent/child . - i-one
  • But in parent / child all children, including siblings. And we need only those in which only one of the parents is the same, and the other parent - the other - Mike
  • @Mike, no, in parent / child do not need all children. Native to sibling. The idea is this. If we had a regular tree, then being in a knot, in one direction we have a subtree of children, and in a different chain of parents. Here too, the other side is a tree (ancestors). Here we make the current node the root, and we put the child and parent trees into it, plus sibling and spouse, which we can also follow. However, it is not necessary to sort through all the links, for example. stepping into the spouse should be excluded spouse as a further step. If you step into parent, you need to move primarily into parent, but you can sibling, except for the child, except the summary ones. - i-one
  • Although spouse / spouse can also be resolved, but only if it is the wife’s ex-husband, i.e. do not allow explicit cycles. - i-one

Thanks for the help, but I will not use any of the options. Another idea occurred to me. Maybe it will be interesting to someone.

So, there is a table with a tree

id / fatherId / motherId

I create the exact same table (in terms of the tree schema) again, but already for kinship links

id / fatherId / motherId / Value (this is the name of the relationship) / gender

it will be filled like this

some_id / father_id / mother_id / self / null

father_id / Null / null / father / null

mother_id / null / null / mother / null

brother_id / father_id / mother_id / brother / male

sister_id / father_id / mother_id / sister / female

And now that's the trick. In the table with a tree there is an initial ID, which is selected during initialization, which means the person around whom the tree is built.

In the relationship table, it matches the row with Value = 'self'

And when I expand the tree in any direction (that is, I make a query to the table with the tree), I use the same filter for the gender related table (or gender = null if gender does not matter). Something like this. Not yet implemented, but it should turn out :)

If the query to the tree table looks like this

SELECT * FROM Trees WHERE id = @ root_fatherId

then the query to the relationship table will look like this

SELECT * FROM FamilyTies WHERE id = @ self_fatherId AND id! = @ Self AND gender = @ finded_gender

And it is possible to stir up generally

  • You would have a record for grandfather let's say led. And it is impossible to imagine what will lie in it. Well and half-brother for example - Mike
  • @Mike Step-brother - mother_id is the same father_id different, and the floor is male. Or vice versa. The grandfather is that father is in father_id. In general, it is necessary to call the columns simply Parent1 and parent2, and the grandfather is either grandmother, brother or sister to decide by sex - iRumba
  • And the "other id" is what? I ask this because I cannot imagine how you will compare the two trees. Specifically, as you see the record of a real half-brother, you will understand that it is the “this id” - “other id” that should be contacted for kinship - Mike
  • @Mike, these are trees with the same structure. It's like going through two identical mazes at the same time. Look at one figure, hold two ... see where to move the first one, and also move the second one without looking) - iRumba
  • I understood approximately what you want, but still it does not reach how the coincidence of conditions will be checked, i.e. was it worth going along this branch? And most importantly, I can not imagine how to portray it in sql and preferably in a single query. In general, beckon when there will be a less working request, it is interesting to take a look - Mike