How to properly maintain relationships?

H

Horned Baphomet2015-02-23 13:55:14

Database

Horned Baphomet, 2015-02-23 13:55:14

Dear comrades, please turn me in the right direction ~~and give me a good kick~~ . Situation: there is a base, there is a table in it, each record is information on one person (primary key - id). It is necessary to go through it, identify and save relatives. The selection criteria are defined, the only question is about maintaining relationships between records in one table. I can’t figure out how to do it correctly - so that if A is a relative of B, then B will automatically be A’s relative. In theory, are we talking about graphs here? Maybe then these links are not stored in a relational database at all? I would be grateful for any advice (including RTFM, if there is a link to FM). Beaver everyone.

Reply

Answer the question

In order to leave comments, you need to log in

3 answer(s)

S

Saboteur, 2015-02-23
@saboteur_kiev

Better yet, a separate plate with relations, where you can list all the connections.
If you store everything in one table, then either not all types of kinship will be indicated, or for each type of column ..?

M

mayorovp, 2015-02-24
@mayorovp

As far as I understand, your task is to store an undirected graph. Moreover, the main difficulty lies in its lack of orientation:
Solve this problem in two ways. First, we can split each edge into two opposite arcs. In other words, add entries to the relationship table in pairs.
An alternative option is lists of incidents. Then there will be two tables in the database - people and relations between them, and between them there will be a link table, with the number of the person and the number of the relationship.
True, both options do not look very nice. But here I have a counter question - why, in fact, did kinship relations suddenly become symmetrical? There are only two basic relationships - "parent-child" and "spouses". Moreover, only the first relation refers to consanguinity - and it is asymmetrical. The second relation is probably not worth considering at all - because of its bad properties (for example, it leads to non-transitive relationship - for example, the ex-husband and current husband are not relatives). All other family relations are unambiguously derived from the basic ones.
Even if it is necessary to store any relationships, not just basic ones (for example, in conditions of incomplete information), most kinship relationships remain asymmetric.

I

Igor Kalashnikov, 2015-02-23
@zo0m

add a column to the table: parent_id in which the parent id will be stored.
I processed such pieces with hierarchical requests (used Oracle)
docs.oracle.com/cd/B19306_01/server.102/b14200/que...
but it is possible and more usual tools.