hi,

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.

Any help would be great,

Pam

You are broadly there - you just need to piece things together. Are the access rights specific to the project per user or does a user get the same access rights to all projects they are a user of?

The user is assigned specific access rights to each project when the application form is completed...

Ignore access for now. Break the problem down.Therefore you need....

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)

and

Users (user_id, first_name, last_name, email address, access_level)

There is a problem in that one project has many users and each user can belong to many projects...

Pam

This is not a problem - this is just a particular type of relationship.

I will give you two clues:
1) Users does not belong in Project
2) You need another table.

Have a read through this and keep in mind your problem:
http://www.tonymarston.net/php-mysql...se-design.html

Just had an idea to create a table that would just hold the details:

Table (Primary Key, Project_id, User_id)

Have no idea if this will work or not but the tables would be linked...

Pam

also the link doesn't open

http://www.tonymarston.net/php-mysql...se-design.html

Pam

Yep - that's called a junction table (and one or two other names).

Link works for me. Try:
http://www.tonymarston.net/
then "PHP/MySQL"
then search for "The Relational Data Model, Normalisation and effective Database Design" & click the link