Data schema and query for polymorphic tree in SQL Server

Question

I have run into this problem a couple of times in my career, and have never been very happy with the solution, and am presented with it again on a project I am doing in ASP.Net MVC, C#, SQL Server 2008:

Imagine I have a Person type (class). I further have types Mother and Father that extend Person. Father and Mother are very similar: they both have a property called "Children", which is a collection of type Person. A "person" can either be represented with the base Person class, the Mother class, or the Father class. From this example you can see that I have both inheritance (is-a) and association (has-a) relationships going on.

I want to build and store family trees in a SQL Server using these OO types. I think I want to have these 3 tables: Person, Mother and Father. Every object will have an entry in Person, and possible and entry in Mother or Father if appropriate (with a FK relationship to Person). Additionally, I will need some crosswalk tables to store the relationships between a Mother record and any child records, and the same with Father.

Does this sound like a good strategy for storage?

How would you efficiently query this for deep and wide family trees?

The problem I get hung up on is the polymorphic nature of the data being returned at and given node in the tree. If this were just a tree of Person objects I would use a Recursive Common Table Expression. But 3 different shapes of data might be returned for any give node, which I would like to map to one of the 3 OO types in C#. I can obviously do the recursion in C# or a stored procedure, but I am not very happy with the performance of such solutions in the past. Additionally, how do I insert records naturally? I have always have to insert in the right order (Person, then Father or Mother) in the past because of the FK relationship enforcement in SQL Server.

Is there a framework out there that will handle this type of ORM for me?

Edit:

To make it clear, the solution I require is to retrieve the entire family tree for display, and be able to add and edit nodes to the family tree. I think the best solution would be to do this in one query in a deep tree. How to design the schema, store, and retrieve is what I am asking about?

Answer 1

Any reason why you can't use a single table with a foreign key to itself?

Answer 2

The only part that I can give an adequate answer is about the ORM framework. NHibernate is really good at handling table inheritance. For example if you make your mappings correct, it will save a Person entity for each Mother or Father that you save.

Check out Inheritance Mapping

Answer 3

You don't need to set a mother and a father to a Person, you just need to associate 2 parents for each Person. Identify the Person with an attribute 'Sex' and the Male will automatically be the Father and Female will be Mother.

Answer 4

I don't see how your specific case is hard. If the code for Father and Mother do not behave differently, you do not need separate classes. You may not even need to subclass person at all.

If do find that you do need a superclass and two subclasses, and you desire the table diagram to be somewhat readable (as opposed to GUID'ing every single primitive property in a flat table), you can have subclass A and subclass B data in their respective tables with foreign key relationship to the table with the supertype data.

If a Has-A relationship is subtype dependant, you'd put it as a column in the appropriate subclass table with a foreign key constraint to person.

Am I missing something? Maybe it would help if you describe what problems you are facing when implementing this solution.