Charper 8: Is the tbl_project_user_role table really needed?

Archives Agile Web Application Development with Yii1.1 and
1

This has been discussed before but not in a way that answers my question.

As jefftulsa put it:

Yes. With you 100%.

But why does this necessitate the [font="Courier New"]tbl_project_user_role[/font] table? It seems to me that our bizrules can query the original 4 TrakStar tables to answer the questions: “Is user [font="Courier New"]U [/font] an owner of project [font="Courier New"]P[/font] ?” “Was issue [font="Courier New"]I [/font] requested by user [font="Courier New"]U[/font] ?” and the rest.

In other words, can’t we write a perfectly good [font=“Courier New”]function isUserInRole($role){}[/font] without querying [font=“Courier New”]tbl_project_user_role[/font]?

What’s data is missing from [font=“Courier New”]tbl_project[/font], [font=“Courier New”]tbl_issue[/font] and the three CDbAuthManager tables that prevents us from doing the same job that Chapter 8 does?

2

There are 2 tables that determine the relations between Users, Roles and Projects:

  1. tbl_project_user_role (project_id, user_id, role)

  2. tbl_project_user_assignment (project_id, user_id)

I also see a repetition there: users and projects are associated in both tables.

We surely need an association project + user + role (defined by tbl_project_user_role), but it could also be defined through the AuthAssign table, i guess…

Actually, it seems that we can then get rid of one associative table: tbl_project_user_assignment or tbl_user_role.

3

I am evaluating this myself right now and do not understand why two tables are needed (or used by the author). It seems to me that one table should suffice:

tbl_project_user

  • project_id

  • user_id

  • role (role_id)

  • create_time

  • create_user_id

  • update_time

  • update_user_id

I suppose the question to be addressed is whether a project user can have more than one role? For my needs, each project user should have exactly one role always, especially since the RBAC can be designed so that higher roles (such as Owner) can inherit "abilities" from lower roles (such as Member or Reader).

4

Thinking on this a bit more, the proposed table structure seems to work even if a project user could have more than one role, as the table’s composite primary key could be modified from:


PRIMARY KEY (`project_id`,`user_id`)

to:


PRIMARY KEY (`project_id`,`user_id`,`role`)