I have a requirement such as every user is assigned some company branch location such as A,B,C and is also assigned account_type such as D,E,F,G,H. There is a table named customer codes which contains the customer_code name, code etc along with the branch which maybe A or B or C or All and account type which may be D or E or F or G or H or ALL. So my goal is that when USER 1 logs in he should only be able to view the customer codes, name associated with branch (A or B or C) and account_type (D or E or F or G or H or ALL).
I have a table structure in mind such as
User_branch_act_type
---------------------------------------------------
Sr-no | Username | Branch | Account Type |
---------------------------------------------------
1 | User 1 | A | F,H |
1 | User 2 | C | D,E.F |
1 | User 3 | A,B,C | E,H |
1 | User 4 | B,C | G,E |
---------------------------------------------------
Customer Codes
--------------------------------------------------------------------------
Sr_no | Customer Name | Customer Codes | Branch | Account_Type |
--------------------------------------------------------------------------
1 | Customer 1 | 566 | B | D |
1 | Customer 2 | 545 | A | G |
1 | Customer 3 | 512 | C | E |
1 | Customer 4 | 589 | A | H |
1 | Customer 5 | 537 | B | F |
--------------------------------------------------------------------------
So for instance User 1 searches for the code of the customer he should only be allowed to search for the customers whose branch is A i.e Customer 2 and Customer 4 and at the same time he should only be able to view the customer codes whose account type is F or H. Therefore User 1 should be able to get the customer code of Customer 4.
Any advice is appreciated. Is the table structure feasible enough to retrieve the data or should I modify it? Thanks in advance.