Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Database design xml vs single table vs multiple tables

    I have 4 tables:

    1) forms (i.e. form1, form2)

    2) designations (i.e. Manager, supervisor, employee, etc)

    3) departments (IT, Human Resources, Administration, etc)

    4) users (which consists of list of users)

    The administrator of the system should be able to set permission to view or edit a form based on designations (and / or) departments (and / or) users

    For example form1 can be accessed by all the supervisors and all the users in Human Resources Department and IT Department and user-24 and user-36 and all users who are Managers

    I can follow one of the 3 routes to attain the above functionality:

    1) Have a column in forms table of datatype xml and have all the allowed criteria in the form of xml

    <allowable_criteria>
    <departments>
    pk_id of HR Department
    pk_id of IT Department
    </departments>
    <designations>
    pk_id of supervisors
    </designations>
    <users>
    pk_id of user-24
    pk_id of user-36
    </users>
    </allowable_criteria>

    The obvious disadvantage of this approach is we cannot maintain the foreign key relationships and xml querying is more complicated when compared to database queries on tables.

    2) Have a table which contains combination of permissions

    pk_id | form_id | department_id | designation_id | user_id

    1 | 123 | 1 | NULL | NULL

    2 | 123 | 2 | NULL | NULL

    3 | 123 | NULL | 45 | NULL

    4 | 123 | NULL | 56 | NULL

    5 | 123 | NULL | NULL | 24

    5 | 123 | NULL | NULL | 36

    3) Have different tables for each of the relationship

    pk_id | form_id | department_id

    pk_id | form_id | designation_id

    pk_id | form_id | user_id

    and to determine whether the form is accessible in a particular situation we can go query on each of the tables, do a union-all and find out.

    4) Have on table where we have the linkable entity defined in 2-columns i.e.

    Pk | form_id | relationship_type | relationship_id

    1 | 123 | “Designation” | 2

    2 | 123 | “Designation” | 45

    3 | 123 | “Department” | 101

    4 | 123 | “User ” | 56

    5 | 123 | “User ” | 7898

    Disadvantage again being no foreign key relationships can be maintained, etc.

    Please help me decide on a scalable and reliable solution

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Usually "security" (granting permissions, etc) is managed by the Security Admins - not the application.

    How are other database applications implemented in your organization? Suggest that your new application follow the way things are currently implemented. Most organizations have standards that outline how these kinds of issues will be done.

    Suggest you talk with your DB Admins and/or Security Admins

Tags for this Thread

Posting Permissions

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