Designing tables for various types of contacts

Today I got three sources of contacts in my system:

• Users of the system
• An addressbook
• A CRM system

Those are stored in different tables. I'm switching to nhibernate from an inhouse ORM solution and nhibernate got great inheritance support. I'm therefore considering to following:

1. Create a base table with all common fields
2. Create one table per contact type (system, addressbook, crm) with specific fields + a link to the base table row (base_user_id or similar). addressbook, crm).

The great thing with such solution is that it's a lot easier to sync each source. Importing a user from the addressbook to the CRM system is simply to create a CRM table and link it to the user in the base table. Modifying the user in the addressbook automatically开发者_开发知识库 modifies it in the CRM.

It will make it easy to add other sources too and keep everything in sync.

My question is: Can you see any problems with such solution?

---

Join jungles will be a problem, leading to performance issues. The database does not need this configuration, and will be slower when this configuration is forced upon it.

The reason database veterans will often criticize ORM is that it is (at least from our point of view) backwards, the persistence of a class hierarchy to a database is far less efficient than designing the tables and then fixing class code on top of them.

Why not just create classes that reflect a design that is efficient as-is?

---

Using one table per hierarchy is simpler, but it gives up 2 capabilities:

• You may want other tables to have a foreign key to just one contact type, but using table-per-hierarchy, this isn't possible. They have a reference to a contact, and that's it. You can get around it with a composite key, but that throws away the simplicity.
• Your derived class columns must be nullable in the database

I've tried both. I prefer table-per-hierarchy over table-per-subclass because I like the simplicity of having them all in one table.