I am designing a database at the moment for my dissertation, I have designed it partly but I am a bit stuck. I am creating an online application form using PHP and MySQL. I have a table called project which will hold details such as project ID, Project Code, Project Manager, Start date etc.
The problem that I am having is that each project has 1-20 users (members of staff) and each of the staff can be granted different access rights.
I have created a seperate table staff (Staff ID, Name, email address). I am guessing that I should also create a table with the access levels (access_level_ID, access_level). The only problem is I don't know how to link these to the project table as there will be more than one user per project and each user will be assigned a certain access level.
Really need more information, but here is a simple group/ role base access:
Assign Project to Application
Assign Group to Application
Assign Role to Group
Assign Role to Application
Assign Permission to Role
Assign Permission to Group
*** Need to add rules if Role contain in group and has higher permissions which is used role or group?
Assign user to role
First, you need to determine what you are going to base your access on - user, role, group, other...
Next, you need to know how you are going to access the data in the database. . I personally do not like to allow direct access to my database especially when queries are from internet. I suggest two service components on to determine access and the other to perform CRUD services. I would grant access only to the services which would access the database on the user's behalf. If a hacker successfully gets into your system and the only access allowed is via services you will be able to determine what data may of been compromised. (It is more involved than I made it sound).
Hi, the database will only be accessable by the admin users. The application form is for project managers to use when they are requesting a new project created in a testing tool. It's all a bit complicated.
The only thing I really need help with is somehow linking the following tables. I have decided to assign each user a default access level:
Project (project_id, name, start_date, end_date, project_manager, users (there are up to 20 users per project)