Multiple Foreign keys to a single table and single key pointing to more than one table

Question

I need some suggestions from the database design experts here.

1. I have around six foreign keys into a single table (defect) which all point to primary key in user table. It is like:

   defect (.....,assigned_to,created_by,updated_by,closed_by...)
   

   If I want to get information about the defect I can make six joins. Do we have any better way to do it?

2. Another one is I have a states table which can store one of the user-defined set of values. I have defect table and task table and I want both of these tables to share the common state table (New, In Progress etc.). So I created:

   • task (.....,state_id,imp_id,.....)
   • defect(.....,state_id,imp_id,...)
   • state(state_id,state_name,...)
   • importance(imp_id,imp_name,...)

There are many such common attributes along with state like importance(normal, urgent etc), priority etc. And for all of them I want to use same table. I am keeping one flag in each of the tables to differentiate task and defect. What is the best solution in such a case?

If somebody is using this application in health domain, they would like to assign different types, states, importances for their defect or tasks. Moreover when a user selects any project I want to display all the types,states etc under configuration parameters section.

Answer 1

1 ...

There is nothing inherently wrong with this. If the possible user "roles" are strictly determined and unlikely to change, then this is, in fact, the preferred way to do it. You are effectively modeling the C:M relationship, where C is constant.

On the other hand, if the roles can change (e.g. you need to be able to dynamically add a new "lifecycle phase" to the defect), then a link (aka. junction) table, modelling the true M:N relationship, might be justified. Something like this:

BTW, while JOINs have their cost, that by itself doesn't mean you can't afford it. And you may not even need all the JOINs all the time - just do the JOINs that bring the information that you currently need and leave the others out. In any case, I recommend you measure on realistic amounts of data to determine if there is an actual performance problem.

2 ...

If there are many common fields, you can use inheritance¹ to minimize the repetition of common fields and foreign keys. For example:

In this model, each "attribute" table (such as state and importance) is connected only once with the base, and through it to each of the "inherited" tables such as defect and task. No matter how many "inherited" tables you add, you'll still have just one connection per "attribute" table

This model essentially prevents the proliferation of "attribute" FKs, for the price of somewhat more cumbersome management of defects and tasks (since they are now split to "common" and "specific" portion). Whether this is a better balance than the old design is for you to decide...

---

¹ Aka. category, subclass, generalization hierarchy etc...