Databse Design problem

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

Database views hierarchy

Hello:
In my SQL Server databases I have complex related tables and views.
There is any tool which allow you to see (print) this complex hierarchy of
views and tables as hierarchical tree?

Thanks,
GBHave you tried the SQL Server Diagraming tools ? This will at least be
able to give you data model to work with. I recall somewhere that
coding some nifty SQL SMO may help.

MJKulangara
http://sqladventures.blogspot.com|||I tried to create database diagrams but it shows table relations only.

GB

"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1138914523.816837.42350@.o13g2000cwo.googlegro ups.com...
> Have you tried the SQL Server Diagraming tools ? This will at least be
> able to give you data model to work with. I recall somewhere that
> coding some nifty SQL SMO may help.
> MJKulangara
> http://sqladventures.blogspot.com|||GB (v7v1k3@.hotmail.com) writes:
> In my SQL Server databases I have complex related tables and views.
> There is any tool which allow you to see (print) this complex hierarchy of
> views and tables as hierarchical tree?

There are several data-modelling tools on the market, that are quite
more eloquent and powerful than the built-in diagamming in SQL Server.
They are also more expensive...

Anyway, the leaders in this field are PowerDesigner (from Sybase), ErWin
(now from Computer Associates) and Embrocadero's tool. I use PowerDesigner
myself, but I don't think it has that much support for showing relation
for views, actually. Then again, I use views very little, so I may have
missed how it works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx