The other similar questions do not solve my problem. This is nothing to do with the PK.
My app is for salespeople to make quotes built with Web2Py. I have products with a monthly cost and a purchase cost and some with both.
The output is 2 separate tables (monthly and purchase), the salespeople want to be able to change the order the products appear on the quote. They also need to be numbered sequentially in the output.
However, as a product may be in the offer but only have a monthly cost and visa versa or even both costs. The order columns look like this:
1 0
2 1
3 0
0 2
0 3
0 4
Which is all fine until a product needs to be removed from an offer.
If for example the second item is deleted. I need to update both sequences. The sequences are not very long. 20 max.
Is there a better way to store the ordering? If not, is there a neater solution than retrieving and updating every record in a offer?
The short answer is no. Thanks CL for the link. This solves the problem but as the article states, it is a method which will eventually break, however this VERY unlikely in this situation. A user would have to spend a very long time changing the order for this to happen and each sequence is only available to one user and his management.
I have decided to use the divide by 2 method as it reduces the Database load and follow up with maintenance re-doing the sequences as suggested in the article.
Considering the scope of the project. I feel the solutions fits.