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
pk_id of HR Department
pk_id of IT Department
pk_id of supervisors
pk_id of user-24
pk_id of user-36
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
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