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
TypeD tables and each entry in each table can have more than one contact. The alternative I thought of are:
- Make a
Contactstable. Make each entry have a
refIDwill 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
ID=1, one should query the
Contactstable from code like:
contacts = Contacts.Where(refID==1 && type=="B")because we do not have referential relationship.
- Make a
Contactstable. Add the columns:
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.
- Create contacts tables for each seperate type like:
TypeDContactsand have them reference the type tables. That way, we can again use the
contacts = someType.Contactsfrom 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.
- Creating a single Contacts table but creating association tables for each of the types. That way, we can use the
contacts = someType.Contactsfrom 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?