Database Design Question for a weird use case

Question

I am having trouble figuring out a good database design for an app I am developing. I have included a description of the related use case in the image below.

How would you design the Work Package in the database? A Project is made up of many Work Packages, which is fine, but a Work Package can also be made up of smaller Work Packages, which can be made up of even smaller Work Packages? How do you design such a 'nested self' relation in the database?

Thanks for the help!

Answer 1

I would approach it this way. Have a "ParentWorkPackageID" that links back to the parent work package. Add as many additional properties as you need to the work packages.

You can easily (using a recursive CTE) query for a work package and any children it may have. You don't mention your engine so I'm not going to provide an example.

You could optionally do away with the Project table completely and simply assume that any workPackage with NULL for a Parent must be the top level workPackage/Project. But I think keeping Project as a separate table will be cleaner since there are probably attributes that apply to projects and not to sub-work packages.

EDIT

I corrected the syntax (sorry about the check constraint error). I've also included an example for you.

DROP TABLE IF EXISTS dbo.WorkPackage
DROP TABLE IF EXISTS dbo.Project 


CREATE TABLE dbo.Project
    (
    ProjectID INT NOT NULL IDENTITY(1,1)
    , ProjectName NVARCHAR(100) NOT NULL 
    , CONSTRAINT PK_Project PRIMARY KEY CLUSTERED (ProjectID)
    )

CREATE TABLE dbo.WorkPackage
    (
    WorkPackageID INT NOT NULL IDENTITY(1,1)
    , ProjectID INT NOT NULL 
    , ParentWorkPackageID INT NULL 
    , WorkPackageName NVARCHAR(100) NOT NULL
    , CONSTRAINT PK_WorkPackage PRIMARY KEY CLUSTERED (WorkPackageID)
    , CONSTRAINT FK_WorkPackage_ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID)
    , CONSTRAINT FK_WorkPackage_ParentWorkPackageID FOREIGN KEY (ParentWorkPackageID) REFERENCES dbo.WorkPackage (WorkPackageID)
    )

GO

INSERT INTO dbo.Project 
(ProjectName)
VALUES ('TestProject')

INSERT INTO dbo.WorkPackage 
(ProjectID, ParentWorkPackageID, WorkPackageName)
VALUES (1, NULL, 'First Work Package')
    , (1, 1, 'First child Package')
    , (1, 2, 'First grand child Package')
    , (1, 1, 'Second Child')

/** Easy to find all packages for a project... **/
SELECT * FROM dbo.WorkPackage WHERE ProjectID = 1

/** Find all children for a specific work package using recursive CTE
    - adds a Generation column so you can see parent to child descendants, regardless of when they were created.
    **/
;WITH CTE_FindChildren AS
    (
    SELECT WP.WorkPackageID AS WorkPackageID
        , WP.ParentWorkPackageID AS ParentWorkPackageID
        , Generation = 1
    FROM dbo.WorkPackage AS WP
    WHERE WP.ParentWorkPackageID IS NULL 
    UNION ALL
    SELECT WP.WorkPackageID AS WorkPackageID
        , CWP.WorkPackageID AS ParentWorkPackageID
        , Generation = Generation + 1
    FROM dbo.WorkPackage AS WP
        INNER JOIN CTE_FindChildren AS CWP ON CWP.WorkPackageID = WP.ParentWorkPackageID
    )
SELECT WP.*
    , CF.Generation
FROM CTE_FindChildren AS CF
    INNER JOIN dbo.WorkPackage AS WP ON WP.WorkPackageID = CF.WorkPackageID
WHERE WP.ProjectID = 1
ORDER BY Generation, WorkPackageID 

Answer 2

There are two main approaches, Adjacency List and Nested Set.

Adjacency list. In this approach there will be a field (an attribute) in the work package table that references a different row in the same table. This is a foreign key, and should be declared as such. WorkPackage.MasterID references WorkPackage.ID.

This is the standard way of representing a many-to-one relation ship in relational design. The relationship is said to be reflexive because the reference is within the same table.

Nested Set. In this approach there are two ID fields, which I'll call ID and LastChild. ID in this scheme has not only identity but also order. All of the IDs between ID and LastChild are children or children of children etc. of the entry in question. Bottom level WorkPackages have LastChild set equal to ID. You can read up on Nested set in Wikipedia.

Adjacency list is easy to maintain, but difficult to use. Many Database systems have special features for traversing the tree when Adjacency list is used. These features make it a lot easier.

Nested Set is difficult to maintain but easy to use. Getting the subtree for any given ID is very easily done using WHERE...BETWEEN. The subtree for a Bottom Level Work Package will consist of just one entry, the entry itself.