0

I have a database of projects and their parent companies that manage them, each project and company has lists of departaments and countries. However if project has field DEFAULT set to true, the list of dep/ countries should be loaded from its parent company instead.

My question is what would be the best method to design this ? Is it possible to do it "correctly" in MySQL or should i just manage it in application code (not too good).

tshepang
  • 12,111
  • 21
  • 91
  • 136
n00b
  • 5,642
  • 2
  • 30
  • 48
  • Might the same country/department be simultaneously assigned to multiple projects? What about to multiple companies? – eggyal Dec 10 '13 at 11:32
  • a project belongs to ONE company, they both have one/many countries and departments (departments are NOT children of companies, they are more like categories internal to the system). – n00b Dec 10 '13 at 11:34

1 Answers1

1

You can use a conditional expression in your join criteria. For example, using MySQL's IF() function:

JOIN departments ON departments.departmentID = IF(projects.default,
    company_departments.departmentID,
   projects_departments.departmentID
)
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • wow i assumed (i assume too much) that you cant do conditional joins. How would I go about defining foreign keys in such setup? – n00b Dec 10 '13 at 12:44
  • @n00b: It isn't a conditional join... the `departments` table is *always* joined to your query, it's just that the join criteria that are used for pairing together records is contingent on the value in `projects.default`. Both the department relationship tables (i.e. `company_departments` and `projects_departments` in my example) would contain an FK into the `departments` table. The situation for countries would be similar. – eggyal Dec 10 '13 at 18:58