table Normalization

Question

I am working on timesheet application and have question on normalization.

I have linked three tables in option-I and in the option-II there is no Group and SubGroup table, just one Activity table.

I like to know which option is better according to the following layout.

ADDED - New Option

Answer 1

I would go for a solution with one GROUP table and one ACTIVITY table:

GROUP_ID (PK)   PARENT_GROUP_ID (FK)   CODE          NAME
--------------- ---------------------- ------------- ------------
1               (null)                 4             IT ACTIVITIES
2               (null)                 5             ADMINISTRATION
3               (null)                 6             FINANCE
4               (null)                 7             HR
5               1                      4.1           IT ADMIN
6               1                      4.2           IT NETWORK
7               2                      5.1           MEETING


ACTIVITY_ID (PK)   GROUP_ID (FK)  CODE     NAME
------------------ -------------- -------- -----------
1                  5              4.1.1    IT PROC
2                  5              4.1.2    HELPDESK
3                  6              4.2.1    TECHNICAL SUPPORT
(etc)

Each table would use an integer surrogate key (running number) for primary key so you don't break referential integrity if you have to change the code for a group or an activity. The foreign key on column PARENT_GROUP_ID referrs back to the same table and column GROUP_ID.

Using a setup like this makes it relatively easy to add more levels in the group hierarchy if that should ever become necessary (and it will, managers always want more detailed reports :-) ). I say relatively easy since you might have to go about updating a lot of the group and activity codes. But you would not have to change (for instance) all timesheets referencing a specific activity since they would be referencing the integer surrogate key.

The same goes for introducing sibling nodes. If you (for instance) need to insert a sub group between 4.2 and 4.3 it will not be too difficult. Just update the code for the current 4.3 group to 4.4 and then insert the new group with code 4.3.

Also, this makes it possible to have activities anywhere in the group tree, if that would ever be needed.