how is your source data laid out? what is the table you are trying to apply security to? you say it is "details". Is this details for all employees (that is managers, senior employees and junior employees)? If so is there a key to this table ? (EmployeeID for example)?
how will you receive your hierarchy information? or is that for you to decide? is the hierarchy "ragged" or are there always the same 3 levels (manger, senior, junior)?
I have one mastertable ‘Employees’ that contains demographic info (or state info) of allemployees. This includes the fields – employee_id, name, date_of_birth,address, phone_number.
As each managerand senior employee is also an employee of the organization, their demographicinfo is also stored in this same table.
I have anothertable ‘Employee Manager’ which maps each employee id with his manager’s id. So,this table has the fields – employee_id and manager_id, with employee_idas the foreign key to the ‘Employees’ table.
I have a thirdtable called ‘Work Hours’ which contains the number of hours each employee hasworked, per day. This table has three fields – date, employee_id andwork_hours.
The abovehierarchy is somewhat static, with 3 levels.
In the light ofthe above data model, how do I achieve the requirements 1 through 3 above? Justto add, I am required to provide the access controls while dashboard viewing,as well as pdf reporting.
I have fullflexibility in editing the data model, and can add/remove fields andtables if required.