Sql-server – DB Design Challenge

database-designentity-frameworkMySQLsql server

I don't have much experience designing databases and I have this case that I stumbled upon where I hope there is a definite answer in terms of DB design methodologies. I also have to mention that I am using an ORM framework (Entity Framework) for querying the database.

I have more than several different types of entities where each type can hold several Contacts. e.g. TypeA, TypeB, TypeC, TypeD tables and each entry in each table can have more than one contact. The alternative I thought of are:

  1. Make a Contacts table. Make each entry have a refID and type column. refID will reference the PK in the corresponding Type table but since it can reference any one of the types, it is not a FK. In order to look up the contacts of a TypeB record with ID=1, one should query the Contacts table from code like: contacts = Contacts.Where(refID==1 && type=="B") because we do not have referential relationship.
  2. Make a Contacts table. Add the columns: TypeAID, TypeBID, TypeCID, TypeDID. Only one should have a value. (And may be add a check constraint on the table) This way we can assign them FKs. So it would be possible to query like: contacts = someType.Contacts. This seems to be the right way with regard to coding but in terms of DB design, it does not seem scalable when you have many other types and seems redundant.
  3. Create contacts tables for each seperate type like: TypeAContacts, TypeBContacts, TypeCContacts, TypeDContactsand have them reference the type tables. That way, we can again use the contacts = someType.Contacts from code. But this will require creating a seperate table for each type even though each table will have the same columns, which again seems redundant on the DB side.
  4. Creating a single Contacts table but creating association tables for each of the types. That way, we can use the contacts = someType.Contacts from code but now again we will have an extra table for each type. Just like option 3.

Is there a better alternative than the ones I mentioned above? What would be the right way to design this database and why?

Best Answer

I would go with your third proposal if contacts do not exist without an entity and to be able to enforce referential integrity.

create table TypeA (
    id int not null identity(1,1)
  , details varchar(64) not null
  , constraint pk_TypeA primary key clustered (id)
  );

create table TypeAContacts (
    typeA_id   int not null
  , contact_id int not null identity(1,1) /* replace this with a good natural key */
  , contact_details varchar(64) not null
  , constraint pk_TypeAContacts primary key clustered (typeA_id, contact_id)
  , constraint fk_TypeAContacts_TypeA foreign key (typeA_id) 
  , constraint uc_TypeAContacts unique(contact_details)
  );

I would go with your fourth proposal if the contacts will always carry the same columns for all entites, and are able to exist without an entity, and to be able to enforce referential integrity.

create table Contacts (
    id int not null identity(1,1)
  , details varchar(64) not null
  , constraint pk_Contacts primary key clustered (id)
  );

create table TypeA (
    id int not null identity(1,1)
  , details varchar(64)
  , constraint pk_TypeA primary key clustered (id)
  );

create table TypeAContacts (
    typeA_id   int not null
  , contact_id int not null
  , constraint pk_TypeAContacts primary key clustered (typeA_id, contact_id)
  , constraint fk_TypeAContacts_TypeA foreign key (typeA_id) 
  , constraint fk_TypeAContacts_Contacts foreign key (contact_id) 
  );

For database design, my favorite books are by Louis Davidson; he has some slide decks and demo code on his website.

Related Question