How to save an order (permutation) in an sql db
- by Bendlas
I have a tree structure in an sql table like so:
CREATE TABLE containers (
container_id serial NOT NULL PRIMARY KEY,
parent integer REFERENCES containers (container_id))
Now i want to define an ordering between nodes with the same parent.
I Have thought of adding a node_index column, to ORDER BY, but that seem suboptimal, since that involves modifying the index of a lot of nodes when modifying the stucture.
That could include adding, removing, reordering or moving nodes from some subtree to another.
Is there a sql datatype for an ordered sequence, or an efficient way to emulate one? Doesn't need to be fully standard sql, I just need a solution for mssql and hopefully postgresql
EDIT To make it clear, the ordering is arbitrary. Actually, the user will be able to drag'n'drop tree nodes in the GUI