CODEWITHSUNDEEP
mysqlsqldatabasesql-server-2008database-design
Anirban Paul
Anirban Paul

Reputation: 1165

Database design for hierarchical department structure

I am trying to develop a database to store and retrieve dynamically created hierarchical department structure. Each department can have a set of child departments or a set of child attributes but can not have both. At run time, user creates a department and children of department. In case a department has attributes as children, the user specifies the type(int/string/bool),size for each attribute.The user can specify any number of attributes as children of a department. So the department structure may be

RootDep
   Dep1
   Dep2
     attribute1(string,12)
     attribute2(int,10)
   Dep3
     Dep31
     Dep32
        Dep321
   Dep4
     Dep41
        attribute4(bool,size not applicable)
        attribute5(string,60)
        attribute6(string,10)
        attribute19(int, 5)   
     Dep5

I need to save this hierarchical structure in database. And latter on, the user can also add data for departments Dep2 and Dep41 that have attributes as children. So I guess I need to dynamically generate tables for departments as and when user adds such a department with attributes.

The user has also provision to modify structure. e.g, add new departments/attributes,remove departments/attributes, rename department name,attribute names. The user can also modify records entered for any department that have attributes as children.

Please let me know the best way to design database for this purpose.

Upvotes: 0

Views: 2023

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52127

To ensure a child department can only be connected to an attribute-less parent and an attribute can only be connected to a child-less department, you could do something like this...

enter image description here

With the following constraint on the DEPARTMENT table:

CHECK (
    (PARENT_TYPE IS NULL AND PARENT_NO IS NULL)
    OR (PARENT_TYPE IS NOT NULL AND PARENT_NO IS NOT NULL AND PARENT_TYPE = 1)
)

And the following constraint on the ATTRIBUTE table:

CHECK (DEPARTMENT_TYPE = 2)

For a department to have attributes, it must be type 2 (to avoid violating the second CHECK), so it cannot have child departments (since that would violate the first CHECK).

For a department to have child departments, it must be type 1 (to avoid violating the first CHECK), which also forbids it from having any attributes (since that would violate the second CHECK).

A childless and attribute-less department can be either type 1 or type 2.

The other issue is how to store the attribute value.

  1. One way is to always encode it as a string and store the "original" type alongside it:

    enter image description here

  2. Or you could have a separate field for each of the available types, with a CHECK ensuring exactly one of them is non-NULL:

    enter image description here

  3. Or go completely overboard and use separate table for each attribute type.

I think you'll be best-off sticking with (1) or (2).

Upvotes: 0

mmertel
mmertel

Reputation: 397

I wouldn't recommend dynamically generating tables for this. You could do it with two tables - one for departments and one for attributes:

create table dept (
    dept_id int,
    parent_dept_id int null,
)

create table dept_attribute (
    dept_id int,
    attribute_id int,
    value_type int,
    value_length int,
    int_value int null,
    string_value varchar(255) null,
    boolean bit
)

Upvotes: 0

Related Questions