Hello all,
I have a database design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryrPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
table .. <<
Then this is not a hierarchy. It should be in one table. You should
not use any kind of auto numbering in a relational database and a GUID
is the worst choice.
How much research did you do when you decided to use NVARCHAR(50) and
NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
all.
children's but he can also access to member B4 of level 2 <<
I have implemented a security scheme with a nested sets model in which
privileges were inherited down the tree from a superior to a
subordinate.
You seem to have no rule for determining privileges, so you will have
to list all combinations.
I have an entire book on TREES & HIERARCHIES IN SQL which might help.|||> How much research did you do when you decided to use NVARCHAR(50) and
> NVARCHAR(255). Thise "magic numbers" are a sign of no design work at
> all.
This is not "Magic numbers" there are the result of an extraction. i am not
responsable of the other database and my design is subbordinate by the other
apps
> children's but he can also access to member B4 of level 2 <<
> I have implemented a security scheme with a nested sets model in which
> privileges were inherited down the tree from a superior to a
> subordinate.
> You seem to have no rule for determining privileges, so you will have
> to list all combinations.
> I have an entire book on TREES & HIERARCHIES IN SQL which might help.
Wich book?I am very interested|||>> Which book? I am very interested <<
TREES & HIERARCHIES IN SQL (Morgan-Kaufmann, 2004)
--CELKO--
