mardi 5 mai 2015

How to structure this relational database

Basically I'll be having 3 tables that have relation. They are: users, departments and company.

The issue I have is this:

  • A company can have many departments
  • A department can only be attached to one company
  • A user can only be part of one company
  • A user can be part of many departments

This is essentially what the table relation would look like:

                    ____________________
                    | | | |            |
                    | | | |            |
--------      --------------      -----------
| user |      | department |      | company |
--------      --------------      -----------
 |   |         | | | | |               |
 |   |         | | | | |               |
 |   ___________________               |
 |                                     |
 |                                     |
 |                                     |
 _______________________________________

The above multiple | lines show an option, so the "company" above has 4 departments and so on.

Now my question is this, How should I structure the relation tables?

Should I have user_departments, user_company and company_departments tables?

That would essentially look like this:

--------------------
| user_departments |
--------------------------------
| id | user_id | department_id |
--------------------------------

----------------
| user_company |
-----------------------------
| id | user_id | company_id |
-----------------------------

-----------------------
| company_departments |
-----------------------------------
| id | company_id | department_id |
-----------------------------------

Or are there any other alternatives for me to consider/implement instead of the path I'm going as it seems it'll just keep growing complex?

Aucun commentaire:

Enregistrer un commentaire