Vance M. Allen wrote:
Good point. My situation is this.. I basically have a Security table, and
currently the ENUM is listing the different actions that are available, and
the table has a simple Y/N for whether each of the access groups is
permitted for the action. If you have any good suggestions or examples for
how to handle this, it would be greatly appreciated.
You need another table:
CREATE TABLE privilege (
access_group_id INTEGER NOT NULL
REFERENCES access_group(access_group_id),
priv_type_id INTEGER NOT NULL
REFERENCES privelege_type(priv_type_id),
permitted BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (access_group_id, priv_type_id)
);
CREATE TABLE privilege_type (
priv_type_id INTEGER NOT NULL PRIMARY KEY,
priv_type_name VARCHAR(64)
);
Now you take all the priveleges info out of your access_group table, and
rely on the rows in the privelege table.
This allows you to add new types of privileges without changing your
schema, and also allows you to look up the list of currently valid
privilege types by "SELECT * FROM privelege_type".
Absence of a row in the privilege table for a given priv type could be
equivalent to not having that privilege. Or if you want to get fancy,
you can make certain privileges have different defaults. Add a
"default_permitted BOOLEAN" to the privilege_type table, and use a query
like the following to get all privileges for all access groups:
SELECT g.id, pt.priv_type_id,
COALESCE(p.permitted, pt.default_permitted) AS permitted
FROM access_group AS g
INNER JOIN privilege_type AS pt
LEFT OUTER JOIN privilege AS p ON pt.priv_type_id = p.priv_type_id;
(note the rare appropriate use of a Cartesian product!)
Regards,
Bill K.
