CHECK contraint

Hi,
Hi, this is the situation.. I have somewhat simplified the tables here that
are insignificant to the structure.
There are organizations, who can be assigned different codes and users (by
an administrator).
These users can access all codes that are assigned to the same organization
that the user himself belongs to.
However, these users (parents) can create sub-users, and assign them a
SUBSET of all the codes that the parent user itself has access to.
So the administrator creates an organization "O" and and assignes user "A".
And assignes codes 1,2 and 3 to this organization. So user A has access to
codes 1, 2 and 3.
Then user A may create one or more users, such as user B, and assign it a
subset of the codes it has access to, such as 2 and 3.
While this type of nesting could go on, in reality we only have 3 types of
users, Admin, A-type users (created by Admin), and B-type users (created by
A-type users).
This is what I came up with:
-- organizations
CREATE TABLE organizations (
`organizationid` INT UNSIGNED PRIMARY KEY
`name` VARCHAR(100)
)
-- each organization can be assigned multiple codes
CREATE codes (
`codeid` INT UNSIGNED PRIMARY KEY,
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid)
)
-- admins, A-type users and B-type users
CREATE TABLE users (
`userid` INT UNSIGNED PRIMARY KEY,
`userparentid` INT UNSIGNED FOREIGN KEY users (`userid`),
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid`)
`name` VARCHAR(50)
)
-- map B-users to subset of codes that its parent has access to
CREATE users_codes (
`userid` INT UNSIGNED NOT NULL FOREIGN KEY users (`userid`),
`codeid` INT UNSIGNED NOT NULL FOREIGN KEY codes (`codeid`)
)
This is how I differentiate between Admins, A-type users and B-type users.
Admins don't have a parent id (NULL) and no organization id (NULL).
A-type users have Admins as parent id or NULL, and non-null
organizationid's.
B-type users have parentid's pointing to A-type users and non-null
organization id's.
There's some duplication because B-type id's can get the organizationid from
its parent (A-type) user too.
codes are assigned to organizations, and to B-type users only, not to A-type
users or Admins.
Somehow I am not comfortable with this model. It seems like I am doing
something wrong and there's a better solution.
Anyway, if I do use this model, I need some CHECK constraints on
users_codes, that makes sure that B-type users (with parent-id) can only be
assigned codeids that their parents (A-type users) have access to.
How do I put this in some kind of CHECK constraint?
Please advise on the structure / model, as well as the CHECK constraint(s)
required.
LisaHow about a standard nested set model with nodes that hold the various
codes
CREATE TABLE Tree
(node_id INTEGER NOT NULL
REFERENCES Nodes(node_id),
lft INTEGER NOT NULL UNIQUE
CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY,
code_1 INTEGER DEFAULT 0 NOT NULL
CHECK (code_1 IN (0,1)),
code_2 INTEGER DEFAULT 0 NOT NULL
CHECK (code_2 IN (0,1)),
code_3 INTEGER DEFAULT 0 NOT NULL
CHECK (code_3 IN (0,1)),
code_4 INTEGER DEFAULT 0 NOT NULL
CHECK (code_4 IN (0,1)),
code_5 INTEGER DEFAULT 0 NOT NULL
CHECK (code_5 IN (0,1))
);
The rule seems to be that you can only inherit codes from a superior.
You could write this with a CREATE ASSERTION in SQL-92; SQL Server
needs a stored procedure that checks for allowed codes before doing an
insertion of a subordinate.|||Interesting concept, but I am having some difficulty grasping how this fits
with my situation. Is the treestructure just for codes only? Users point to
a certain node? An organization (and subusers) could be assigned up to 65536
codes. So it has to be a set, not fields in a table (like the 5 code fields
in your example).
I think my model is more like what I need, but what makes me feel
uncomfortabe is that 3 types of users, Admins, A-type and B-type users, are
all in the same users table. The type of user can only be recognized and
distinguished indirectely by whether organizationid and/or userparentid is
NULL or not.
And A-type users are not linked directely to codes, but are linked to
organizations, and codes are also linked to organizations and via the
organization, A-type users are INDIRECTELY linked to these codes.
But B-type users require a subset of codes, thus they have to be linked
DIRECTELY to the codes table:
( A-type users )===>( Organizations )<===( Codes )<===( B-type users )
Anyway, let me ask you another question... can check constrains refer to
other tables?
CREATE TABLE A (
val INT
)
CREATE TABLE B (
val INT CHECK( val > A(val))
)
something like this?
Lisa
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141180347.823645.141760@.t39g2000cwt.googlegroups.com...
> How about a standard nested set model with nodes that hold the various
> codes
> CREATE TABLE Tree
> (node_id INTEGER NOT NULL
> REFERENCES Nodes(node_id),
> lft INTEGER NOT NULL UNIQUE
> CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE,
> CHECK (lft < rgt));
>
> CREATE TABLE Nodes
> (node_id INTEGER NOT NULL PRIMARY KEY,
> code_1 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_1 IN (0,1)),
> code_2 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_2 IN (0,1)),
> code_3 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_3 IN (0,1)),
> code_4 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_4 IN (0,1)),
> code_5 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_5 IN (0,1))
> );
> The rule seems to be that you can only inherit codes from a superior.
> You could write this with a CREATE ASSERTION in SQL-92; SQL Server
> needs a stored procedure that checks for allowed codes before doing an
> insertion of a subordinate.
>