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...
> 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
> >
> >
> >

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...
> > 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
> > >
> > >
> > >
>
>
>

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
>
>