2

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?

  • 2
    There's not one way that fits all problems, it all depends on your business. Do the different type of entities share some information, do they have something in common? – Lennart Dec 26 '16 at 15:39
  • Entities do not have anything in common but I would like to have your suggestions for both cases. – John L. Dec 26 '16 at 15:46
  • Could a contact be more than one type eg a student and a teacher, or both a doctor and a patient at the same time? – Sir Swears-a-lot Dec 29 '16 at 8:46
  • @Peter No, a contact can be of a single type only. – John L. Dec 29 '16 at 10:41
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.