NHibnerate data design recommendations

Question

While doing some research into mapping one-to-one relationships I've come across some statements that have made me question some of my database design decisions.

Basically I have some entities similar to the following:

Person, Contact, Parent

Both a contact and a parent are people. A person may be a contact, parent, both, or neither.

The database design I came up with has a table for each of these three entities, and all three tables share a primary ID (PersonID). From a database design perspective, this seems to be a well-normalized and reasonably performant way to represent the database and its relationships (at least to me).

At this point, I begin coding C# classes and NHibnerate mappings to represent these entities. The most natural mapping approach I can find is to use a mapping. The other options (one-to-one, one-to-many, etc...) seem to require that I add one or more unnecessary FK's to the tables. Upon browsing through the NHibernate documentation I stumble upon the following statement:

This feature is often only useful for legacy data models, we recommend fewer tables than classes and a fine-grained domain model. However, it is useful for switching between inheritance mapping strategies in a single hierarchy, as explained later.

My question is:

A) Am I violating this principal? B) If so, how would I better design this system?

Is this statement suggesting that I should lump all of the Person/Contact/Parent fields into a single table (with many nullable fields)? Or am I somehow missing the point?

Since this is a rare occasion where I can design the tables/classes from scratch I would like to get it right. Thanks in advance for the help!

Edit: More info about how I intend the above database design to work:

The basic idea is that every person gets a record in the person table. The presence/absence of records in the related tables determines whether the person is a parent, contact, etc... This would seem to enforce the one->one relationships and allow for fast queries/joins (the shared primary ID would be a clustered PK in each table).

Edit: Thanks for the help guys. I didn't really consider queryability well enough when I designed this system so I'm going to move toward something similar to the solutions suggested by Jamie Ide & hlgem. I found all answers helpful. All in all it looks like shared primary keys result in some problems with the object model on the c# side.

Answer 1

Your database design needs a bit of work; here's my suggestion.

It's good that you've got the idea that Contact and Parent can both be Persons; however, you need for Contact and Parent to have distinct Primary Keys. You can enforce the requirement that they both have Parent IDs with a non-nullable Foreign Key in the Contact and Parent tables that refers to the Person ID of the entry in the Person table.

So the Person table should have a unique Primary Key (its ID) and any other relevant columns. The Contact table should have its own unique Primary Key (its ID), a non-nullable Foreign Key reference to the Person table (the ID in the Person table) and any other relevant columns. The Parent table should have its own unique Primary Key (its ID), and a non-nullable Foreign Key reference to the Person table (the ID in the Person table) and any other relevant columns.

This should solve your mapping problems. If you need, you can use a View to "recombine" your Person / Parent / Contact "constellation" (collection of related tables).

Answer 2

You're asking about inheritance mapping, not a one-to-one relationship. Your design requires table-per-subclass mapping, but I'm unsure how to address the requirement that a Person can be both a Contact and a Parent. I don't know of a good way to model this except to introduce another subclass covering both roles. Think of it this way, if you ask your repository to return a Person and that Person is both a Contact and a Parent, which type should it return?

If you do stick to subclassing then I would recommend sticking to table-per-class if possible.

But a better design might be to introduce a "role" table and have a one-to-many relationship between Person and Role.

Edited to add: IMHO, your current design won't work. What type of object would represent a Person who is both a Contact and a Parent?

Instead of a simple role table, you could use a "complicated" role table(s) that included the additional data. You could have a set of Role objects (ContactRole, ParentRole, etc.), each containing the fields associated with that role. A Person object would then have a collection of Role objects.

Answer 3

Some of this depends on how you intend to query that data. If you want to easily be able to see what roles a person plays wihout the details specific to a role, then have a roles table. To see all the roles a person has, just two tables need to be joined no matter how many roles you later add. Otherwise, you will have to join to every one of the specialty tables (left joins in case they don't exist) just to see what roles a person has. Not so good for performance.

We have lots of these types of relationships in our database and we have a person table with an id, a roles table with the id and the role id in it and a role type table that is a lookup for the roles. And then specialty tables that have the information for a specific role such as sales reps or sales targets. Each table has it's own id and each table (except the lookup table) includes the id from the person table which is set as a foreign key. (Trust me there is very rarely such a thing as unnecessary foreign keys, the converse is not true, missing an FK when you should have one is bad news.)

Now you could still have the foreign key relationship using just the PK from the parent table as both the Fk to the person table and the PK of the specialty table but I recommend against it. In any event, if you don't set up an FK to the person table, you data is doomed to data integrity issues. I prefer to use the surrogate keys and thus always set my own key on each table. It prevents many redesign issues in the long run. If you want to maintain the one-to-one relationship, a simple unique index on the field is all you need. The table moves from one-to-one to one-to-many and all you need to do is drop the index. If you were using it as your PK as well, then you need a new PK and all the code referencing the pk has to be changed. Adding a new PK to a table with 150,000,000 records, not a fun task.