postgresql - Query and modify list of children with arbitrary positions

Question

With the following schema in mind:

parent (
  parent_id
  child_id
)

child (
  parent_id
  child_id
  position
)

I try to design a schema that allows me to retrieve a list of children, where children can be ordered arbitrarily. Adding a child to a parent is the same as appending it to the end of list, with its position equal to the maximum position of all positions for a given parent + 1. One should also be able to re-position a child with respect to its siblings, here, by changing the position columns of multiple child entries. One should also be able to delete a child, with their respective positions staying unchanged.

Querying the children of a given parent according to their position would follow this type of query:

select child.* 
  from child 
  where child.parent_id = $1 
  order by child.position asc;

I wonder whether this sort of schema would be adequate, as any manipulation, whether insertion of a child, or changing the position of a child, becomes quite complicated, as it involves querying/modifying existing records at first. Should we instead place the position data onto the parent table, as an array of some sort?

标签： postgresqldatabase-design

Answer 1

There are two common ways of modelling hierarchical date in relational database - the adjacency list and the nested set. Joe Celko has an entire book dedicated to this.

Your proposed model is based on the adjacency list. It's intuitive, but often hard to work with - retrieving a hierarchy requires recursive queries, and updates/inserts can be complicated.

The alternative is the nested set - which is much more efficient when querying, and inserts/updates are simpler (though do require more records to be modified).