hello,
how must a database structure be to respresent a company schema like
described below ?
for example :
i have a table with 26 department names :

Name
dept A
dept B
dept C
...
dept Z

Every company is free to make its own company schema.
Company X

dept A
----- dept B
----- dept C
---------- dept B
--------------- dept N ---> added latest
---------- dept K
---------- dept Q ---> added later
----- dept M
----- dept P
---------- dept S
--------------- dept U
--------------- dept K

.... a.s.o.

it must be dynamic. Every department can be chosen from the table above. I
tried to solve it this way :

table Deptartments :
ID..NAME..JDEPT..TAB
1.......A.......0.......0
2.......B.......1.......1
3.......C.......1.......1
4.......B.......3.......2
5.......K.......3.......2
6.......M.......1.......1
7.......P.......1.......1
8.......S.......7.......2
9.......U.......8.......3
10.....K.......8.......3
11.....Q.......3.......2
12.....N.......4.......3

(ID is autogenerated (incr +1 with every insert), Name = dept Name, DEP =
the department ID this department belongs to,
if TAB = 0 the department can have 3 sub-departments
if TAB = 1 the department can have 2 sub-departments
if TAB = 2 the department can have 1 sub-department
if TAB = 3 the department can't have a sub-department
i need a resultset back from the database which would give me the company
structure in the appropriate order. The problem is, the latter added dept Q
belongs before dept M and right after dept K, the latest added dept N before
dept K and after dept B. I have to query the Departmens table 4 times (one
for every tab number 0, 1, 2 and 3) to get 4 resultsets back. To display the
schema i need a while loop containing 3 other while loops (one for every
resultset).
Second problem :
i wrote a trigger with the following sql statement :
update t_st_departments set state = n.state where ID in
(select ID from t_st_departments where jdept in
(select ID from t_st_departments where jdept in
(select ID from t_st_departments where jdept in
(select ID from t_st_departments where ID = N.ID)))
or jdept in
(select ID from t_st_departments where jdept in
(select ID from t_st_departments where ID = N.ID))
or jdept = N.ID)
(n is the name for the updated row)
db2 gave me a SQL0954C error, so i had to increase the application heap size in the database configuration. the old value was 128, i set it to 2048. Just to get my trigger work and it worked. are such long statements appropriable ?
However i think there must be a better database schema for my problem.
Would be nice if someone could explain me a better solution.
Thanks in advance.