Loading ...
Sorry, an error occurred while loading the content.

Doing UPDATE on "inherited" records?

Expand Messages
  • Jakob Vedel Adeltoft
    Hi there, I have 4 tables: *permission_list *permission_data *project_list *project_inherit_list These tables have the following columns: .:: permission_list
    Message 1 of 1 , Mar 1, 2003
    • 0 Attachment
      Hi there,
      I have 4 tables:

      *permission_list
      *permission_data
      *project_list
      *project_inherit_list

      These tables have the following columns:

      .:: permission_list ::. (a list of projects and attached users)
      permission_id (int) [auto incrementing]
      ref_project_id (int) [reference to a project]
      ref_user_id (int) [reference to an user]

      .:: permission_data ::. (a list of what kind of permission the users have for each project)
      ref_permission_id (int) [reference to permission_id in table permission_list]
      ref_permission_type (int) [what kind of permission ie. read(0), write(1), modify(2)]
      access (bit) [does the user have deny(0) or allow(1) access]

      .:: project_list ::. (a list of projects)
      project_id (int) [auto incrementing]
      ref_project_id (int) [this is ID for it's parent project, 0=root]
      name (char 255) [name of project]

      .:: project_inherit_list ::. (a list of projects and if they should inherit permissions from their parent or not)
      ref_project_id (int) [reference to a project]
      inherit (bit) [does this project inherit from its parent or not? no(0), yes(1)]

      Now I wan't to add a user to a project and set permissions. That's not difficult, but what I can't figure out is how to add this user and his permissions to all child projects that inherits from this project.

      I've attached a sample table below of "project_list" and "project_inherit_list":

      ## project_list ##
      (project_id, ref_project_id, name)
      1 0 ProjectA
      2 1 ProjectB
      3 1 ProjectC
      4 3 ProjectD

      ## project_inherit_list ##
      (ref_project_id, inherit)
      1 0
      2 1
      3 1
      4 1

      This is how I wan't "permission_list" and "permission_data" to look like when adding the userID=45 to projectID=1 (ProjectA):

      ## permission_list ##
      (permission_id, ref_project_id, ref_user_id)
      10 1 45
      11 2 45
      12 3 45
      13 4 45

      ## permission_data ##
      (ref_permission_id, ref_permission_type, access)
      10 0 1
      10 1 1
      10 2 0

      11 0 1
      11 1 1
      11 2 0

      12 0 1
      12 1 1
      12 2 0

      13 0 1
      13 1 1
      13 2 0


      Now, how do I insert this user so I get above result? Also how do I later on remove this user again or change his permission, so all inherited projects still will be affected by the change?

      Look forward hearing some comments on this one :-)

      Best Regards
      Jakob
    Your message has been successfully submitted and would be delivered to recipients shortly.