Parent / Child page DB structure opinion

I am looking at setting up a pages which would have parent and child(s) pages, but what would be the best DB structure to support this, in your opinion etc.

All page would have identical page table structure and data EG: title, copy, date, image etc etc . So woudl one do it like:

(A) Single Table method

        |-----|-------------|----------|--------|
        |  id |  parentid   | title    |  etc   |
        |-----|-------------|----------|--------|
        |  1  |  null       |  a title |  etc   |
        |  2  |  null       |  a title |  etc   |
        |  3  |       2     |  a title |  etc   |
        |  4  |       2     |  a title |  etc   |

So the eg above records 3 & 4 are child pages of page is 2

Or to completely separate both into their own Model and Table:

ParentPages table and ChildrenPages table and link them using the hasMany and belongsTo calls etc.

I would tend to probably go for the second version of two separate tables, but thought best to ask and get others opinions and what others have tried or share. Thanks in advance

spekkionu

9 years ago

Level 48

Are you ever going to have anything deeper than 2 levels? Are any of your child pages going to have children of their own?

If so the two table approach isn't even an option.

Even if you wont be going deeper I highly recommend not having more than one table. Even if the initial setup might be a little steeper it will be much easier to work with in the long run.

There are two common patterns for storing hierarchical data in a relational database.

The first is the Adjacency List model which is what you posted as your first example . The advantage to this patter is that it is very easy to move a node from one parent to another. It is also easy to understand and doesn't require any fancy algorithms to work with. The disadvantage is that pulling the full tree is a much heavier operation that can often require recursion or extra looping and thus is pretty slow.

The second is the Nested Set pattern. This is a bit more difficult to explain so I'll just link to wikipedia. https://en.wikipedia.org/wiki/Nested_set_model

The advantage of the nested set pattern is that you can pull the full tree easily and thus faster. The disadvantage is that it isn't a very easy algorithm to understand at first. It is also a lot more expensive to move or modify nodes as more than one record needs to be updated. There are two good laravel implementations of the nested set model, Laravel Nestedset and Baum. These would take most of the difficulty out of implementing the pattern.

If you wont ever need anything deeper than just parent child pages the adjacency list model is probably a better fit but as soon as you go deeper I would recommend using the nested set pattern.

1 like

simondavies

9 years ago

Level 26

@spekkionu thats great thanks for the great response. Well I would like to say that it would just be the single sub tier but, then in a few months, it'll be can we add a sub-section this this page please, which in turn is already a sub page, :-) So might be best to have a look at the latter option to future proof etc the system.

Much appreciated and thanks

Please or to participate in this conversation.