Parent/Child Relationship Table Design - What's The Best Practice?

Question

I have a single table for storing 'Tasks'. A task can be a parent and/or a child. I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have a parent task.

Example is the screenshot below...

It has been argued in my team, that it would be much better (for normalization/best practices) to create a separate table to store the ParentIDs and so avoid having NULLS in the table and lead to better normalization design.

Would this be a better option? Or will it be more difficult with querying and cause performance issues?

We just want to get the design right from the beginning rather than finding issues later.

SQL-DDL code for the existing table:

CREATE TABLE [Tasks].[TaskDetail]
(
    [TaskDetailID] [int] IDENTITY(1,1) NOT NULL,
    [TaskName] [varchar](50) NOT NULL,
    [TaskDescription] [varchar](250) NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_TaskDetail_IsActive] DEFAULT ((1)),
    [ParentID] [int] NULL,
    CONSTRAINT [PK_TaskDetail_TaskDetailID] PRIMARY KEY CLUSTERED ([TaskDetailID] ASC),
    CONSTRAINT [FK_TaskDetail_ParentID] FOREIGN KEY([ParentID]) REFERENCES [Tasks].[TaskDetail]([TaskDetailID])
);

Answer 1

The technique you are describing for representing task hierarchy is called 'Adjacency list'. Although it is the most intuitive to humans, it doesn't lend itself to very efficient querying in SQL. Other techniques include path enumeration (aka materialized paths) and nested sets. To learn about some other techniques, read this post or search the web for numerous articles on these techniques.

SQL Server offers a native hierarchy representation for path enumeration. This is most likely your best bet...

Answer 2

There is no rule of normalization that prohibits null values or would require storing the adjacency list in a separate table. Both approaches are common, and there are not significant performance implications to your choice.

Whichever design you choose, remember all foreign key columns need to be supported by an index. So you'll need an index on ParentID to support efficient traversal down the hierarchy.

Answer 3

From SQL Server 2017 and Azure SQL DB you can use the new graph database capabilities and the new MATCH clause to model this type of relationship. Look no nulls! A sample script:

USE tempdb
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'Tasks' )
EXEC('CREATE SCHEMA Tasks')
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'graph' )
EXEC('CREATE SCHEMA graph')
GO

DROP TABLE IF EXISTS [Tasks].[TaskDetail]
DROP TABLE IF EXISTS graph.taskDetail
DROP TABLE IF EXISTS graph.isParentOf 
DROP TABLE IF EXISTS graph.isChildOf 
GO

CREATE TABLE [Tasks].[TaskDetail]
(
    [TaskDetailID] [int] IDENTITY(1,1) NOT NULL,
    [TaskName] [varchar](50) NOT NULL,
    [TaskDescription] [varchar](250) NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_TaskDetail_IsActive] DEFAULT ((1)),
    [ParentID] [int] NULL,
    CONSTRAINT [PK_TaskDetail_TaskDetailID] PRIMARY KEY CLUSTERED ([TaskDetailID] ASC),
    CONSTRAINT [FK_TaskDetail_ParentID] FOREIGN KEY([ParentID]) REFERENCES [Tasks].[TaskDetail]([TaskDetailID])
);
GO

SET IDENTITY_INSERT [Tasks].[TaskDetail] ON 
GO

INSERT INTO [Tasks].[TaskDetail] ( TaskDetailID, TaskName, TaskDescription, IsActive, ParentID )
VALUES 
    ( 2, 'Cash Receipt 1', 'Fund Account', 1, NULL ),
    ( 3, 'Cash Receipt 2', 'Check the ...', 1, 2 ),
    ( 4, 'Non Trade', 'Income & Expense', 1, NULL ),
    ( 5, 'Income Verified', 'Income Verified', 1, 4 ),
    ( 6, 'Expense Verified', 'Expense Verified', 1, 4 ),
    ( 7, 'Pricing', 'Pricing Verified', 1, NULL ),
    ( 8, 'Manual Pricing', 'Manual Pricing', 1, 7 ),
    ( 9, 'Missing Pricing', 'Missing Pricing', 1, 7 )
GO

SET IDENTITY_INSERT [Tasks].[TaskDetail] OFF
GO


-- Create graph tables
CREATE TABLE graph.taskDetail (
    taskDetailId    INT PRIMARY KEY,
    taskName        VARCHAR(50) NOT NULL,
    taskDescription VARCHAR(250) NULL,
    isActive        BIT NOT NULL 
    ) AS NODE;

CREATE TABLE graph.isParentOf AS EDGE;
CREATE TABLE graph.isChildOf AS EDGE;
GO

--  !!TODO add indexes

-- Add the node data
INSERT INTO graph.taskDetail ( taskDetailId, taskName, taskDescription, isActive )
SELECT taskDetailId, taskName, taskDescription, isActive
FROM Tasks.TaskDetail

-- Add the edge data
INSERT INTO graph.isParentOf ( $from_id, $to_id )
SELECT p.$node_id, c.$node_id
FROM Tasks.TaskDetail td
    INNER JOIN graph.taskDetail c ON td.TaskDetailId = c.taskDetailId
    INNER JOIN graph.taskDetail p ON td.ParentID = p.taskDetailId


-- Add inverse relationship
INSERT INTO graph.isChildOf ( $from_id, $to_id )
SELECT $to_id, $from_id 
FROM graph.isParentOf 
GO


-- Now run the graph queries
SELECT
    FORMATMESSAGE( 'Task [%s](%i) is the parent of [%s](%i)', p.taskName, p.taskDetailId, c.taskName, c.taskDetailId )
FROM graph.taskDetail p, graph.isParentOf isParentOf, graph.taskDetail c
WHERE MATCH ( p-(isParentOf)->c )
ORDER BY 1;


-- Tasks with same parent
-- Tasks 5 and 6 have the same parent 4
-- Tasks 8 and 9 have the same parent 7
SELECT
    FORMATMESSAGE( 'Tasks %i and %i have the same parent %i', t1.taskDetailId, t3.taskDetailId, t2.taskDetailId )
FROM graph.taskDetail t1, graph.isChildOf c1, graph.taskDetail t2, graph.isChildOf c2, graph.taskDetail t3
WHERE MATCH ( t1-(c1)->t2<-(c2)-t3 )
  AND t1.$node_id < t3.$node_id
ORDER BY 1;


-- Find tasks with no parents?
SELECT
    FORMATMESSAGE( 'Task [%s](%i) has no parents.', p.taskName, p.taskDetailId )
FROM graph.taskDetail p
WHERE NOT EXISTS
    (
    SELECT *
    FROM graph.isParentOf isParentOf
    WHERE p.$node_id = isParentOf.$from_id
    )

My results: