Hello !
I would like known your appreciation, and to get some advices and tips
about a design issue about adding ACL notion.
I have several *existing* tables that manages some user's items which
are quite different (postal adresses, photos, climbing_skills,...).
Please note that "Users" are not PostgreSQL users, they are records in a
"member" table.
####################################
-- USER table
CREATE SEQUENCE seq_member_id;
CREATE TABLE members
(
pk_member_id INT8 NOT NULL DEFAULT NEXTVAL('seq_member_id');
... (some user related fields)...
);
-- ITEMS tables
CREATE SEQUENCE seq_item_type_a_id;
CREATE TABLE item_type_a
(
pk_item_id INT8 NOT NULL DEFAULT NEXTVAL('seq_item_type_a_id'),
fk_member_id INT8 NOT NULL,
... (some specific fields) ...
CONSTRAINT fkey_member_id FOREIGN KEY (fk_member_id) REFERENCES
members(pk_member_id)
);
CREATE SEQUENCE seq_item_type_b_id;
CREATE TABLE item_type_b
(
pk_item_id INT8 NOT NULL DEFAULT NEXTVAL('seq_item_type_b_id'),
fk_member_id INT8 NOT NULL,
... (some other specific fields) ...
CONSTRAINT fkey_member_id FOREIGN KEY (fk_member_id) REFERENCES
members(pk_member_id)
);
####################################
Several years laters, there's a need for theses users to be able to
define ACE (Access Control Entries) for their items. So, I have to add
an ACL (Access Control List) notion.
I thought of two solution :
- Solution A
- Solution B
# SOLUTION A.
####################################
CREATE TABLE acl_ace_item
(
item_type VARCHAR(40) NOT NULL,
item_id INT8 NOT NULL,
ace_label VARCHAR(100) NOT NULL,
CONSTRAINT PRIMARY KEY (item_type, item_id),
CONSTRAINT chk_item_type CHECK item_type IN ('item_type_a',
'item_type_b'),
CONSTRAINT chk_acl_uniqueness UNIQUE (item_type, item_id)
);
NOTA : The 'item_type' field will indicates which table need to be
JOINed. I don't known if I can put a JOIN in a CASE structure, but I
have to give a try or find another way.
PRO : Avoid any changes to the existing 'item_type_XXX' tables.
CONS : a) If the user delete a 'item_type_a', 'item_type_b' record, I
will have orphan record in the acl_ace_item table.
b) No way to have a FOREIGN KEY CONSTRAINT on 'item_id' field
since it can be an id from 'item_type_a', 'item_type_b'.
####################################
# SOLUTION B.
####################################
CREATE SEQUENCE seq_acl_item_id;
CREATE TABLE acl_item
(
pk_acl_id INT8 NOT NULL DEFAULT NEXTVAL('seq_acl_item_id'),
CONSTRAINT pkey_acl_item PRIMARY KEY(pk_acl_id)
);
CREATE TABLE acl_ace_item
(
fk_acl_id INT8 NOT NULL,
ace_label VARCHAR(100) NOT NULL,
CONSTRAINT chk_ace_uniqueness UNIQUE (fk_acl_id, ace_label),
CONSTRAINT fkey_acl_id FOREIGN KEY (fk_acl_id) REFERENCES
acl_item(pk_acl_id);
);
ALTER TABLE item_type_a ADD COLUMN fk_acl_id INT8;
ALTER TABLE item_type_a ADD CONSTRAINT fkey_acl_id FOREIGN KEY
(fk_acl_id) REFERENCES acl_item(pk_acl_id);
ALTER TABLE item_type_a ADD CONSTRAINT chk_uniqueness_acl_id UNIQUE
(fk_acl_id);
ALTER TABLE item_type_b ADD COLUMN fk_acl_id INT8;
ALTER TABLE item_type_b ADD CONSTRAINT fkey_acl_id FOREIGN KEY
(fk_acl_id) REFERENCES acl_item(pk_acl_id);
ALTER TABLE item_type_b ADD CONSTRAINT chk_uniqueness_acl_id UNIQUE
(fk_acl_id);
NOTA : - The 'acl_item' table could appears to be useless, but it
enables me to avoid orphan records in the 'acl_ace_item' items.
- A NULL 'fk_acl_id' value in 'item_type_XXX' tables would means
NO defined ACL, and so free access.
- No records in the 'acl_ace_item' table for an existing acl_id
would also means NO defined ACL, and so free access.
PRO : a) JOIN between 'item_type_XXX' tables and acl_ace_item could be
faster than SOLUTION A.
CONS : a) If the user delete a 'item_type_a', 'item_type_b' record, I
will have orphan record in the acl_ace_item table.
b) No way to have a UNIQUE CONSTRAINT that would cover existing
'item_type_XXX', so it is possible to have a same fk_acl_id value in the
'item_type_a' and the 'item_type_b' table. Which is a situation I would
avoid.
####################################
As you can see, I'm not really convinced for SOLUTION A or SOLUTION B as
they have both some CONS. And they will both require some stored
procedures to check references integrity. Solution B seems to be less
weird to me.
What would you do in that kind of situation ? Which solution would you
take ? Would you uses another design to solve that problem ?
Feel free to leave your appreciation about that problem. Your advices,
tips or some interesting URLs are welcome also !
Thanks in advance !
Kind regards,
--
Bruno Baguette - bruno.baguette@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
