CODEWITHSUNDEEP
.netdatabasec#-4.0graphfamily-tree
Riana
Riana

Reputation: 689

Graph and relational database

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

Upvotes: 2

Views: 776

Answers (4)

Duncan
Duncan

Reputation: 219

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.

Upvotes: 1

BionicCyborg
BionicCyborg

Reputation: 21

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

Upvotes: 2

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

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.

Upvotes: 4

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

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.

Upvotes: 1

Related Questions