CODEWITHSUNDEEP
mysqldatabasedatabase-designrbac
LiuWenbin_NO.
LiuWenbin_NO.

Reputation: 1326

how to design: users with different roles see different records

I have a schema design question for my application, hope I can get advices from teachers. This is very alike of Role Based Access Controll, but a bit different in detail.

Spec:
For one company, there are 4 roles: Company (Boss) / Department (Manager) / Team (Leader) / Member (Sales), and there are about 1 million Customers records. Each customer record can be owned by someone, and he could be Boss or Manager or Leader or Sales. If the record's owner is some Sales, then his upper grade (say: his leader / manager / boss) can see this record as well (but others: say the same level of his workmates, cannot see, unless his upper grade manager share the customer to his workmates), but if the record's owner is boss, none except the boss himself can see it.

My Design is like this (I want to improve it to make it more simple and clear):
Table:
departments:
id (P.K. deparment id)
d_name (department name)
p_id (parent department id)

employees
id (P.K. employee id)
e_name (employee name)

employee_roles
id (P.K.)
e_id (employee id)
d_id (department id)

customers
id (P.K. customer id)
c_name (customer name)
c_phone (customer phone)

permissions
id (P.K.)
c_id (customer id)
e_id (owner employee id)
d_id (this customer belongs to which deparment)
share_to (this customer share to other's id)

P.S.: each employee can have multi roles, for example, employee A can be the manager of department_I and meanwhile he can also be one sales of deparment_II >> Team_X.

So, when an employee login to application, by querying from employee_roles table, we can get all of the department ids and sub department ids, and save them into an array.
Then I can use this array to query from permissions table and join it with customers table to get all the customers this employee should see. The SQL might look like this:

I don't really like the above SQL, especially the "IN" clause, since I am afraid it will slow down the query, since there are about 1 million records or even more in the customer table; and, there will be as many records as the customers table in the permissions table, the INNER JOIN might be very slow too. (So what I care about is the performance like everyone :))

To my best knowledge, this is the best design I can work out, could you teachers please help to give me some advice on this question? If you need anything more info, please let me know. Any advice would be appreciated!
Thanks a million in advance!!

Upvotes: 0

Views: 339

Answers (1)

philipxy
philipxy

Reputation: 15118

Do not use an array, use a table, ie the value of a select statement. And stop worrying about performance until you know more basics about thinking in terms of tables and queries.

The point of the relational model is that if you structure your data as tables then you can looplessly describe the output table and the DBMS figures out how to calculate it. See this. Do not think about "joining"; think about describing the result. Whatever the DBMS ends up doing is its business not yours. Only after you become knowledgeable about variations in descriptions and options for descriptions will you have basic knowledge to learn about performance.

Upvotes: 1

Related Questions