 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / February 2005Database 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. > Hello all, > [quoted text clipped - 34 lines] > 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 > Hi, > Typically, if I want to design a hierarchy that has more than 2 levels, I [quoted text clipped - 50 lines] > > 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. > parent child have too many problems : > 1/Must implement recursivity (bad performance) [quoted text clipped - 56 lines] > > > Thanks > > > cymryr Rather than a seperate table for each table, consider one self-referencing table. > Hello all, > [quoted text clipped - 34 lines] > Thanks > cymryr |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.