best practice for dependency roots

Question

When a database has a dependency table showing the parent-child relationship of items...
I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero or null, and this is the indicator that this item is a top level item aka the root of the dependency tree.

This is what I'm seeing used in a recursive CTE as the anchor member.

However, it seems that requiring this zero or null field might be unnecessary, since the parent item can be deduced by seeing that a given item number appears in the dependency table as a "parent id" but never as a "child id".

Example of deducing item 300 as the root parent of the 300-330 tree:

Dependency table

Parent_id	child_id
300	310
310	320
320	330

Example of the (far more common in examples) use of parent_id = 0 to define the root parent:

Parent_id	child_id
0	300
300	310
310	320
320	330

So the question here is one of "best practices". If I have the ability to design a dependency table from the ground up, is there a reason to go with one structure vs the other?

Answer 1

You can write the CTE anchor any way you like. If you prefer NOT EXISTS syntax over IS NULL, go for it. The anchor could even reference different table(s) to the recursive part if that suited better.

As for performance - assuming you're using an adjacency list and have indexes in place I'd think the null would be faster.

Finding a given value in a BTree is an O(log n) operation. Reading each value in parent_id then seeing if it is present in child_id will be O(n log n).

If you have completely free choice of design performance comes down to the read/ write ratio and what result sets are desired. One of the other implementations may give much better read performance at the cost of additional work during writes.