Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    5

    normalisation and table design

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?

  3. #3
    Join Date
    Dec 2007
    Posts
    5
    The user is assigned specific access rights to each project when the application form is completed...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ignore access for now. Break the problem down.
    Quote Originally Posted by pam_w
    each project has 1-20 users (members of staff)
    Therefore you need....

  5. #5
    Join Date
    Feb 2007
    Posts
    3
    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).

  6. #6
    Join Date
    Dec 2007
    Posts
    5
    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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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

  8. #8
    Join Date
    Dec 2007
    Posts
    5
    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

  9. #9
    Join Date
    Dec 2007
    Posts
    5

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •