2

I'm looking for the best way to store and query a family tree (a graph of people with their relationships like a genealogy ) into a relational database.

I guess, that can be easily achieved by using a graph database but I have a strong constrain.

I use .NET and Microsoft technologies, and the ideal is probably to find some kind of technology that can sit on top of a relational DB so that both can be used at the same time ..

Any suggestions or advices are welcome !

Thanks guys Riana

Riana
  • 689
  • 6
  • 22
  • When you say relational database, do you mean MS SQL server? What kind of queries do you want to perform? – svick Nov 13 '11 at 17:25
  • I'm using MS SQL Server but the problem is shared by all relational DB. I would like to be able to check simple informations such as if two one person is a grandfather of one another or if he is his cousin etc ... – Riana Nov 13 '11 at 17:40
  • And what's stopping you doing that in SQL? – svick Nov 13 '11 at 18:00
  • For sure, it can be achieved using SQL, but I'm looking for a better or easiest way if you will. Thanks – Riana Nov 13 '11 at 20:10

4 Answers4

4

Since any given person can have only one mother and one father (not necessarily both known), you don't need a generalized representation of directed graph. A simple "binary" graph like this should be sufficient:

enter image description here

Querying for siblings, ancestors, descendants etc... should be fairly simple in this model.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • 1
    "any given person can have only one mother and one father" - although true biologically, it doesn't account for adoptions and/or divorces/remarriage (eg step-fathers). – Duncan Sep 28 '12 at 11:23
2

I think there is a Graph database that is targeted towards .Net. It is called BrightStarDB Sparql and LinQ as a means to query.

Bob Kaufman
  • 12,864
  • 16
  • 78
  • 107
BionicCyborg
  • 21
  • 1
  • 2
    Welcome to StackOverflow! Do you *think* that BrightStarDB is a graph database targeted for .Net or do you *know* for sure? Answers should be that: answers, never guesses. Mind you, my first few times out, I guessed myself... and of course I guessed wrong... and was mercilessly downvoted by my fellow participants. Also, when referring to a product like this, it's a good idea to include a link to it. – Bob Kaufman Jul 06 '12 at 19:03
1

gramps (http://gramps-project.org/) is an opensource (http://www.gramps-project.org/wiki/index.php?title=Portal:Developers) genealogy platform. It's written in python and had downloads for working on windows. Depending on your motivations (eg why you need it to be a relational db) it may work for you out of the box, or you may want to use it just to examine it's source code. It has a data abstraction layer so can work with several underlying db's (http://www.gramps-project.org/wiki/index.php?title=Using_database_API). So you can get access to whichever db you use independently from gramps. For example you could use gramps to load all your data to 'make' your database but then use that independently for your queries.

Duncan
  • 219
  • 3
  • 8
1

If you have a table

FamilyTree
----------
ID       int not null PK,
ParentID int,
Name     nvarchar(50)

You can query relationships with simple joins.

This is how to get all siblings to a person with ID=@SearchPersonID

select sibling.* from FamilyTree parent
inner join FamilyTree child
on parent.ID = child.ParentID
inner join FamilyTree sibling
on parent.ID = sibling.ParentID
where child.ID <> sibling.ID
where child.ID = @SearchPersonID

To get cousins you need two levels of joins etc.

To get a whole familytree things gets a little bit more complicated, but you can use a recursive CTE to generate all decendants from a given parent.

Albin Sunnanbo
  • 46,430
  • 8
  • 69
  • 108