Database deign problem

Hello all,
I have a datbase 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
cymryr
Hi,
Typically, if I want to design a hierarchy that has more than 2 levels, I
use a parent-child relationship like that:
[OBJECT]
OBJECT_ID int auto
OBJECT_CODE nvarchar(50)
OBJECT_NAME nvarchar(255)
PARENT_OBJECT_ID int
[OBJECT_PERMISSION]
OBJECT_ID int
USER_ID int
Tomasz B.
"Cymryr" wrote:

> Hello all,
> I have a datbase 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
> cymryr
>
>
|||parent child have too many problems :
1/Must implement recursivity (bad performance)
2/hard to know the level of the member
3/ impossible to have different columns at each level
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...[vbcol=seagreen]
> Hi,
> Typically, if I want to design a hierarchy that has more than 2 levels, I
> use a parent-child relationship like that:
> [OBJECT]
> OBJECT_ID int auto
> OBJECT_CODE nvarchar(50)
> OBJECT_NAME nvarchar(255)
> PARENT_OBJECT_ID int
> [OBJECT_PERMISSION]
> OBJECT_ID int
> USER_ID int
> Tomasz B.
> "Cymryr" wrote:
levels[vbcol=seagreen]
|||Hi,
Of course, you have right, but please remember that, this is a dimension
table and usually it contains much less records than a fact table, so
performance is not an issue – SQL Server can load whole table into memory,
also you should expect minimal insert, update and delete activities.
If you really new a level number, you can define a column called LEVEL and
maintenance this information by trigger
If you want different columns at each level, you can define a view.
Tomasz B.
"Cymryr" wrote:

> parent child have too many problems :
> 1/Must implement recursivity (bad performance)
> 2/hard to know the level of the member
> 3/ impossible to have different columns at each level
> "Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
> message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...
> levels
>
>
|||Rather than a seperate table for each table, consider one self-referencing
table.
"Cymryr" <Cymryr@.hotmail.com> wrote in message
news:evfi5ucDFHA.3888@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have a datbase 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
> cymryr
>