For the discussion, I will provide my example. But I am interested in the broad guidelines.

I save in my postgres DB legislation (law texts) composed of a single header (H), paragraphs (P), sub-paragraphs (SP) and sub-sub-paragraphs (SSP). Every level owns some content, rules, applications, etc. Every block has the same properties.

Thus, a single table in DB primary key called id with self-referencing by way of foreign key parent_id :

SSP points to its SP, SP points to its P and P points to the header H.

Only one Header by law text. All the SSP, SP and P points to a parent row. Only the header does not.

Here I see only 2 options,

  1. we can set to null the referencing column (parent row id) for the header row
  2. OR we can set it to its own id.

Which is the best option ?

Our brainstorming results :

  • Lema : the DB is the absolute reference and it makes no mistakes.
  • Lema: developers are lazy and error prone, they will forgot to check from time to time => the DB should be structured in order to limit error possibilities.

Thus it is desirable that the developer only has to test obvious case.

Null ID option :

3 cases must be considered by the developer :

  • is it null (it's an header) ?
  • is it a row id (and not the header id) ?
  • is it the same row id (the header is self-referencing while it should not) ?

I really don't like that implementation because I am sure many inexperienced developers would forget to check when not null that the parent id is not self-referencing the same row (id != parent_id).

It's adding complexity.

Moreover, I think the data can be corrupted without anyone noticing (every case is OK for the DB).

Code Loops are broken when checking for null parent_id or for parent_id == id (throw/manage data corruption).

Redeeming possibility, it seems one could add a constraint in the DB to forbid that parent_id == id. If if can be done, it would restrain to 2 cases and make life easy again for developers.

Apparently, most old/existing system are using this option : easier for experienced developer to use the pattern.

Some people seem to think it's natural to not point to anything for the highest row and shocking to point to itself.

parent_id is never null and parent_id == id for Headers

Only 2 cases :

  • parent_id == row_id (it's a header)
  • it's different (it's a P, SP or SSP)

Straightforward, a row points to itself or to another row. Elegant.

The DB enforces the not nullable and that's so relaxing to know the DB got your back against corrupting data.

You have to handle the insertion of header in the DB which can be tricky depending on your implementation for connecting the code to the DB (ORM, native SQL, other...). But usually, this code is written once (if not already written by the ORM) and reuse while business/logic code is written for each new feature.

Lesser usage, so experimented developers could be slow down by this pattern.

Also, I have been told that some complex SQL requests/functions might fail to work in this implementation. But I did not get why they could not be modify.


Option 2 is my favourite but if one can add constraint to the DB to block the parent_id == id in option 1, it's more palatable to me (although it still means adding constraints thus complexity to the DB).

We probably missed some points.

For example, performance could be different ? In my use case, performance are irrelevant but for an heavy usage, it might be significant.

What's the best practise and what's the killing argument ?

  • 2
    Set it to null. It doesn't have a parent, and making it its own parent is going to cause all sorts of problems. Endless loops and stack crashes come to mind. – Robert Harvey Jul 1 '19 at 14:44
  • Thanks for the prompt answer. Seeing how much a clear-cut case it seems to you, could you explain more about the "all sorts of problems" ? In either case, one has to check for the no-parent case. – Poutrathor Jul 1 '19 at 14:49
  • 1
    If you make a head node a parent of itself, you've now created a field that has two different meanings. Using null is semantically accurate: This node has no parent. – Robert Harvey Jul 1 '19 at 14:52
  • 1
    I think you're focusing too much on the database constraints. There aren't going to be any for this. Parent ID is just a foreign key, not required. – Robert Harvey Jul 1 '19 at 15:04
  • 1
    Even if you could add a constraint to prevent a self-referencing row, it'd be pretty darned difficult to add a constraint to prevent a cycle (either one would be disastrous to an algorithm designed to walk a tree). Sometimes you have to make sure your application logic is correct and can't rely on constraints to save you. – John Wu Jul 2 '19 at 1:48

What I got from talking with Robert :

Why no row self-referencing ?

  • in the real life we are modelising in the DB, there is no parent to the header : by lemma "we want DB to match closely real life", we want null for parent_id. There is no parent => no parent_id. That's the main argument.

Issue with nullable parent_id : the parent_id == id case must be check for (in case of corrupted data / bug). It can be forbidden with a DB constraint. Imho, it MUST be solved at the DB level, do not trust future devs or sys admins. If the semantic argument is strong enough to justify nullable parent_id, then it justifies the DB constraint. I would even say it's disingenous to opt for parent_id = null without blocking parent_id == id at the DB level (are we commit to modelise real life in the DB or not ?).

With self-referencing row, the SQL query to find the parent requires a supplementary WHERE clause :

select * from rubrique as parentRub
inner join rubrique as startingRub on startingRub.rubrique_id = parentRub.id
where startingRub.id != startingRub.rubrique_id;

In the same way that a developer could forget to check for parent_id == id, a developer could forget to add the where clause. Imho, it's not at the same level : there are more business/logic code written that SQL queries and SQL queries are more often reused than logic code. Still, conceptually, devs could forgot it. BUT, if the dev/tester runs the query at least once, the issue will be detected (duplicate results). Meanwhile for nullable parent_id, without constraint, if the dev forget to check that parent_id != id, the bug will never be detected : the data can be silently corrupted, the corruption will not be detected right away.

To conclude, null for header sticks closely to real life model and that is best. Add a DB level constraint to sleep well.

  • 1
    Note that cyclic references involving more than one row can't be prevented with DB constraints. Those will have to be detected in the software anyway. – Bart van Ingen Schenau Jul 2 '19 at 9:20
  • @BartvanIngenSchenau Iirc MS SQL will let you write a constraint that would walk the references to forbid cycles. It's still a bad idea – Caleth Jul 2 '19 at 12:53
  • It seems one can write cyclic prevention detection SQL to be trigger at each insert/update on the table for other DB : stackoverflow.com/questions/23019384/… – Poutrathor Jul 2 '19 at 12:57

is it the same row id (the header is self-referencing while it should not)

note that self referencing row is only a specific case of more generic corruption: referencing circle. You should rather verify that you dont't have that generic case, and you would have to do it regardless of the option you'd pick. So this argument goes

  • good point, you mean row1 > row2 > ... > row1, right ? I have not thought about this at all ! – Poutrathor Jul 1 '19 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.