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,type_id,.....)
   • defect(.....,state_id,type_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.