Model Recursive relationship without graph database

Question

We use Postgres as our database. Now have a new requirement to model relationships like follow. Though it seems like a good fit for a graph database, there is no room for using graph db as postgres has already been used as db platform in this application.

What's the best way to model this in a relational design to avoid recursive self joins which will result in horrible query performance for a large recursive tree. -

Query requirement is such that display John Doe 's relationship tree along with every related member's relationship tree in there.

Note that following example is for illustration purposes but real use case involves very deep such recursive relationships.

{
"personId" : 1,
"name" : "John Doe",
"relationships": {
  "cousineOf" : 23 //23 is a personId in the same table. say 23 is "Merry",
  "fatherOf" : 3 //where 3 is a personId with name - "George",
  "brotherOf" : 67 //where 67 is a personId with name - "Tony"

 }
}

You haven't explained what sort of queries you're going to be performing and you state that you want to model a recursive relationship without resorting to recursive queries? A bit more detail would be helpful... — Ben, Aug 16 '14 at 12:18
@Ben : just added query requirement. let me know if that answers your question. — user2066049, Aug 16 '14 at 12:23
Normally, you don't store siblings (there are too many of them ...) Just store the id's of the parent(s) in the person record. Plus, of course {gender , DOB , name} etc. Siblings can be derived from this hierarchy. (siblings share the same parents, cousins the same grandparents, etc) — wildplasser, Aug 16 '14 at 12:29
Are you aware of the ability to do recursive queries in SQL? http://www.postgresql.org/docs/current/static/queries-with.html — , Aug 16 '14 at 13:17

score 0 · Answer 1 · edited May 23 '17 at 12:05

This is how you would design the schema:

create table people (
  person_id int primary key,
  name text not null
);

create table relationships (
  from_person_id int references people(person_id),
  type text, --link to foreign table in real life
  to_person_id int references people(person_id),

  primary key (from_person_id, type, to_person_id)
);

Sample data:

insert into people values
(1, 'John Doe'),
(23, 'Merry'),
(2, 'George'),
(67, 'Tony');

insert into relationships values
(1, 'cousinOf', 23),
(1, 'fatherOf', 6),
(1, 'brotherOf', 67);

Please let us know what you want to do with the data. Some tree and path examples using PostgreSQL Recursive CTEs.

How does a Recursive CTE run, line by line?

Model Recursive relationship without graph database

1 Answers1