Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Unhappy need help with database design

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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: need help with database design

    I presume you are not using Oracle, which has tree-structured query option for exactly this sort of case.

    Here's a possible solution, given that your hierarchy is restricted to 4 levels:

    1. Create a view:

    CREATE VIEW v_departments
    ( sort1, sort2, sort3, sort4, print_name )
    AS
    select d0.name, '0', '0', '0', d0.name
    from dept d0
    where d0.tab = 0
    UNION
    select d0.name, d1.name, '0', '0', ' ---- ' || d1.name
    from departments d0, departments d1
    where d1.tab = 1
    and d1.jdept = d0.id
    UNION
    select d0.name, d1.name, d2.name, '0', '-------- ' || d2.name
    from departments d0, departments d1, departments d2
    where d2.tab = 2
    and d2.jdept = d1.id
    and d1.jdept = d0.id
    UNION
    select d0.name, d1.name, d2.name, d3.name, '------------ ' || d3.name
    from departments d0, departments d1, departments d2, departments d3
    where d3.tab = 3
    and d3.jdept = d2.id
    and d2.jdept = d1.id
    and d1.jdept = d0.id;

    2) Select from the view:

    SELECT print_name
    FROM v_departments
    ORDER BY sort1, sort2, sort3, sort4;

    Hopr that helps.

  3. #3
    Join Date
    Oct 2002
    Posts
    37

    Smile thank you

    I'm not that far in sql but your solution helped really.
    that was exactly what i needed.
    i use ibm db2 7.2.
    Thanks for all.

  4. #4
    Join Date
    Oct 2002
    Posts
    7
    quick question . . . have you ruled out LDAP as on option? . . . . you can do recursive SQL procedures to handle these types of problems, but the results are not always pretty!

    -isaac

  5. #5
    Join Date
    Oct 2002
    Posts
    37
    no i haven't. we use a LDAP server but i never heard something about using LDAP in db2.
    could you explain shortly what it means to rule LDAP as on option ?
    and why are the results not always pretty ?
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •