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
p-553 => position
2 etc. No problem so far until I insert a new product between
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?