I am creating a database where the user will enter a series of instructions using text and comboboxes. The user needs to be able to re-order the records manually, eg insert a record in a between two rows, or move a record.
I started by giving each row a unique line number, but of course as soon as you insert a row in the middle of the table that idea falls apart.
I tried using line numbers with lots of decimals; that way I can insert line 6.5 between 6 and 7, then insert 6.75 between 6.5 and 7 etc but that's an ugly solution.
Does anybody know a more elegant way to insert a record between two others and update a unique key to keep them sorted correctly?