Ideas about data normalization

Question

I'm designing an app for work management. The work can be broken down in (technically) infinite levels using a WBS (Work Breakdown Structure).

When defining the job/task numbers for each level, what is the best way to normalize the task/job reference data (i.e. Job Number)?

For example, I have 5 Reference fields for Identifying tasks/jobs. The first three fields identify the job at level 1. The 4th field adds an identifier for the job at Level 2. Obviously, the number of Reference fields is either going to run out soon or I would have to add an insanely large number of reference fields to handle the different levels of WBS.

Any suggestions?

BTW, I am using MS-SQL Server but the concept should be applicable to any db.

Thanks, John

---

more details

I am using a parent child for the WBS level; however, Each level has a distinct Job Number that consists of the job number from the previous level + the identifier for the job at this level.

Example:

Level 1 XXXX-99-1234

Level 2 XXXX-99-1234-A

Where each part in the Job Number is a specific column from the WBS table.

Table:

(

     WBSID INT,
     WBSParentID INT,
     WBSLevelID INT, 
     WBSReference1 VARCHAR (XXXX from above)
     WBSReference2 VARCHAR (99 from above)
     WBSReference3 VARCHAR (1234 from above)
     WBSReference4 VARCHAR (A from above)
....

)

Obviously, I don't wish to keep adding more WBSReferenceX fields to handle all the possible WBSLevels.

Concatenating the values into one field is not an option as we must maintain the distinct identifiers for each level.

Answer 1

I am not sure whether I understand your question, but it seems like you want to store a tree structure in a relational database. You can do this with a parent (or child) column, which indicates the position of the current record in the tree.

Answer 2

I am using a parent child for the WBS level; however, Each level has a distinct Job Number that consists of the job number from the previous level + the identifier for the job at this level.

Example:

Level 1 XXXX-99-1234

Level 2 XXXX-99-1234-A

Where each part in the Job Number is a specific column from the WBS table.

Table:

(

 WBSID INT,
 WBSParentID INT,
 WBSLevelID INT, 
 WBSReference1 VARCHAR (XXXX from above)
 WBSReference2 VARCHAR (99 from above)
 WBSReference3 VARCHAR (1234 from above)
 WBSReference4 VARCHAR (A from above)

....

)

Obviously, I don't wish to keep adding more WBSReferenceX fields to handle all the possible WBSLevels.

Concatenating the values into one field is not an option as we must maintain the distinct identifiers for each level.