Store children in database preserving information about their order

Question

I am designing database tables for a master-detail scenario. The specific requirement is that it is necessary to store information about the order of children. I see some possible solutions (like adding a column representing a position in the sequence, or a column with foreign key to the previous child) but I would like to know the best practices how to solve such problems.

Best regards
Lukasz Glaz

Answer 1

A column for position in sequence is the better option of the two.

With the second you need to do multiple joins or have very complex logic in order to determine the sort order of the children.

When needing to maintain a non textual/numeric sort order, use a numeric sequence column. Add some CHECK constraints to keep the correct logic (i.e. no children of a single parent can have the same sequence number).

Answer 2

In the past I have had an extra column. You can then use a common table expression to recursivly query the table. See this page.

The problem I see with the first option is if you need to rearrage the order or insert something in the middle, you have to renumber all children in positions after the change.