Database Design

Hi,
I'm designing a database with this scenario:
Our application will be used in big organization that each organization has
some sub-organizations. Each sub-organization has divisions(each division
can be beneath another division or beneath a sub-organization directly),
each division has roles and each role has employees:
Main Org >> Sub-Org >> Divisions >> Roles >> Employees
I use adjacency model (like Employee-ReportsTo fields) to handle the chart.
I think there is two main solutions for physical implementation of tables:
1) Creating separate tables for each level: tblSubOrg, tblDevisions,
tblRoles, tblEmployees
Then create another table that keeps the hierarchy information.
2) Creating one table for all of these entities and having all required
fields in that.
Both solutions can help to retrieve the chart but the first one needs an
extra join to get more information about each entity, but the second one
does not need. First solution looks pretty nice but I'm just worried about
it's performance as the tables become larger.
Any help would be greatly appreciated.
Leila
In SQL, there are no operators which can act on an adjacency list directly
to explode a hierarchy ( treesort etc. ). So, you will end up writing a lot
of procedural code ( loops, recursion etc. ) to retrieve the data. A popular
approach is to transform the adjacency list to a nested model where you
represent the nodes in the hierarchy using two additional attributes, left &
right. Search the google archives for "nested sets" and you will find Joe
Celko's examples in this regard.
Anith
|||Thanks Anith,
I read that article but I cannot understand that what Right and Left
indicate for each row? Please help me.
Thanks..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e0DbEzS6EHA.1524@.TK2MSFTNGP09.phx.gbl...
> In SQL, there are no operators which can act on an adjacency list directly
> to explode a hierarchy ( treesort etc. ). So, you will end up writing a
lot
> of procedural code ( loops, recursion etc. ) to retrieve the data. A
popular
> approach is to transform the adjacency list to a nested model where you
> represent the nodes in the hierarchy using two additional attributes, left
&
> right. Search the google archives for "nested sets" and you will find Joe
> Celko's examples in this regard.
> --
> Anith
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23eOiFCU6EHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks Anith,
> I read that article but I cannot understand that what Right and Left
> indicate for each row? Please help me.
> Thanks..
Leila,
Pretend that you have the following hierarchy:
a
/ \
b c
\
d
Now, starting at 'a' and walking left, you're going to increment every time
you hit a node from the left, and every time you hit it from the right:
1 a 8
/ \
2 b 3 4 c 7
\
5 d 6
... and that is what the 'left' and 'right' represent.
This makes it very easy to write hierarchical queries. For instance, to get
all children of C, we simply query WHERE lft > 4 AND lft < 7.
See Celko's posts for many more examples.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Thanks Adam, I got it :-)
But it seems difficult to retrieve nodes only one level lower than a
particular node?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Onv8VfX6EHA.3828@.TK2MSFTNGP09.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23eOiFCU6EHA.824@.TK2MSFTNGP11.phx.gbl...
> Leila,
> Pretend that you have the following hierarchy:
> a
> / \
> b c
> \
> d
> Now, starting at 'a' and walking left, you're going to increment every
time
> you hit a node from the left, and every time you hit it from the right:
> 1 a 8
> / \
> 2 b 3 4 c 7
> \
> 5 d 6
> ... and that is what the 'left' and 'right' represent.
> This makes it very easy to write hierarchical queries. For instance, to
get
> all children of C, we simply query WHERE lft > 4 AND lft < 7.
> See Celko's posts for many more examples.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:eh6HGsZ6EHA.796@.TK2MSFTNGP09.phx.gbl...
> Thanks Adam, I got it :-)
> But it seems difficult to retrieve nodes only one level lower than a
> particular node?
Yes, that's one of the downfalls of the system, IMO. Celko has some queries
in his examples that show how to do it, but some people like to keep both
the adjacency list and the nested sets around simultaneously in order to
make situations like that a lot easier... It's a tradeoff of space vs. ease
of use.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||On Fri, 24 Dec 2004 12:59:09 +0330, Leila wrote:

>Thanks Adam, I got it :-)
>But it seems difficult to retrieve nodes only one level lower than a
>particular node?
Hi Leila,
I think you'd need to do something like this:
SELECT Parent.name, Child.name
FROM Hierarchy AS Parent
INNER JOIN Hierarchy AS Child
ON Child.lft BETWEEN Parent.lft AND Parent.rgt
WHERE NOT EXISTS
(SELECT *
FROM Hierarchy AS Intermediate
WHERE Intermediate.lft BETWEEN Parent.lft AND Parent.rgt
AND Child.lft BETWEEN Intermediate.lft AND Intermediate.rgt)
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||To convert a nested sets model into an adjacency list model:
[vbcol=seagreen]
particular node? <<
SELECT B.emp AS boss, E.emp
FROM OrgChart AS E
LEFT OUTER JOIN
OrgChart AS B
ON B.lft
= (SELECT MAX(lft)
FROM OrgChart AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt);
Another way to think about what (lft,rgt) is that they are a count of
the tags in HTML, or begin-end pairs in a programming language or
parens in algebra.
What is really hard about the adjacency list model is writing the
constraints needed to prevent cycles and forests. Most people never
think about this and just do not bother. Well, that is until one of
their procedural traversals gets stuck in an endless loop.