CakePHP ACL Setup Help

Question

I have a database setup like this (I know it isnt the best setup but its out of my power to change it :-(

Basically there are 3 levels of users - Admin - Managers - Staff

Few notes: -Each member of staff belongs to department -If the Staffs logon_code appears in the manager table they are a Manager, otherwise they are a member of staff -If the staffs logon_code appears in the manager table and SystemAdmin is set to 1, they are Admin

How on earth do I go about setting ACL/Auth for this? Any ideas?

CREATE TABLE tblStaff ( StaffID int(11) NOT NULL auto_increment, dept_id varchar(5) default NULL, logon_code char(10) NOT NULL, forename char(50) NOT NULL, surname char(50) NOT NULL, PRIMARY KEY (StaffID), ) ;

CREATE TABLE tblManager ( ManagerID varchar(15) NOT NULL, logon_code varchar(15) NOT NULL, dept_id varchar(5) NOT NULL, SystemAdmin tinyint(1) unsigned default NULL, PRIMARY KEY (ManagerID) ) ;

CREATE TABLE tblDepartment ( dept_id varchar(5) NOT NULL, sect_id varchar(50) default NULL, subsect_id varchar(50) default NULL, sect_name varchar(50) default NULL, sect_abbr varchar(50) default NULL, subsect_name varchar(50) default NULL, PRIMARY KEY (dept_id) ) ;

Answer 1

I honestly don't see why tblStaff and tblManager should be separated in your example. Aren't they all "employees" i.e. "staff" in some sort of way?

Why not:

create table tblUsers (UserID, logon_code, dept_id, SystemAdmin, firstname, lastname, etc)

After all, regardless of whether they will be "staff" or "manager" they will be "users" of your application.

Additionally, if you need more separation of those, you can add another field (such as user_level_id) which will determine the role of every user. After that, Auth/ACL should be fairly straightforward.