Andrus wrote:
"if department _id is NULL, user has access to all departments data."
This is your problem. You've assigned meaning to the "value" NULL.
CREATE TABLE permission (
id serial,
user_id CHAR(10) NOT NULL REFERENCES user,
permission_id CHAR(10) NOT NULL REFERENCES privilege,
UNIQUE (user_id, permission_id));
CREATE TABLE permission_department (
id serial,
user_id CHAR(10) NOT NULL REFERENCES user,
permission_id CHAR(10) NOT NULL REFERENCES privilege,
department_id CHAR(10) REFERENCES department ,
UNIQUE (user_id, permission_id, department_id));
Any person who is authorized to access documents of a department MUST have
a corresponding row in permission_department: If they are authorized to
view documents of all departments, then they must have a row corresponding
to every department.
I don't understand why the permission_department table is required ?
I didn't include this because I thought it would be obvious: You have to
put a unique constraint on that table so as to eliminate the possibility
of redundant departmental permission rows, as you thought was your
original problem.
If user is authorized to all departments, I can add separate row for each
department to former permission table. So the permission_department table is
not required at all
Except that when abusing the meaning of NULL you can add duplicate rows
indicating permission for all departments redundantly, which is what you
originally misidentified as being the problem for which you sought a
means to put a unique constraint on NULL values. As the first respondent
said, the problem IS with the design.
Unfortunately, this approach causes loss of information: it loses the fact
that user is allowed to
see all departments data. If new department is added, this department should
be made accessible
for all users which have marked as "access all departments".
That information is not lost, but it is a little more work to get it:
You know how many departments there are. Any user that has a count of
departments equal to the number of existing departments is an "all
departments" user. You can thus use aggregation to identify the "all
departments" users and then add a row for them corresponding to the new
department.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
