Mysql Connect Two Tables (FK) - Database Design

Question

Hi People, how can I connect the table project_user with the table project_terms in the best way?

• One Project has different Categorys like 1,2,3,4
• The Categorys are stored in the categorys_table
• But one User maybe are assigned only for the Categorys 1,2 and not 3,4
• Should I connect the project_table with the categorys_table too?

how can I solve that in my Database Design?

[project_user]
id name
1  Tom

// But User Tom only assigned for the Project_id 1
// and the cat_id 2 in the project_terms table

[project_terms]
project_id (fk) | cat_id (fk)
1               | 2
1               | 3


[categorys_table]
cat_id | catname
2      | Lemon
3      | Apple



[project_table]

project_id | name
1          | FruitsProject

Answer 1

No idea if I understood you correctly: One possible approach could be to associate all your users with a project

[project_project_user]
project_id  |   user_id
    1              1

With this, you have a user assigned to a project.

To be able to check the terms, you could introduce a table that matches terms to users.

[project_terms_user]
user_id   |    term_id
    1              2

You could also go as fas as leaving out the [project_project_user] table since the connection can be established via [project_terms_user]. Some queires may be more efficient with the first table available, but the resuls should be the same.

Answer 2