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
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,
- we can set to null the referencing column (parent row id) for the header row
- 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 ?