My problem is, that I have no idea how I could cleanly make an index (not a real Table index, just an indicator) for each table row representing the entries position while each position must be unique but inserting a new entry may not cause everything to change its positions.
Here is an example:
Let's assume we have a table products, where each product has clearly its product-id as primary key. Now I am making a catalog of all products and another categorized by anything. Now I certainly could take the product-id or the product name to sort the lists but I want some of them to appear earlier and some not so I am going to give them indexes.
My first solution would be:
Each product gets its own position index like product p-2151 => position 1, p-553 => position 2 etc. No problem so far until I insert a new product between p-2151 and p-553. Now I had to reposition all following products what is no good idea, because I would have to refresh the complete product-cache of my application to recognize it.
So this is definitely not the way to go.
Normally I have no problems with such almost trivial things but somehow I just don't get it here.
What would be the cleanest way to go here?
Best Answer
No matter what solution you choose, you're going to have to "refresh your product cache" anyway, since cached information won't reflect your new product either. You should let the DBMS do its job and just query against the base table.
In terms of managing arbitrary (manually imposed) sequences, one solution to consider which allows for easy inserts with only periodic recalculation of the whole sequence is to use double instead of int for your sequence number.
When you insert a record between two existing records, set the sequence number of the new record to half way between the existing records. This can go on for a very long time before you have to break down and recalculate the whole sequence.
You could have a nightly/weekly/monthly process that recalculates all of your sequence numbers so that they use natural numbers again.