Suppose I'm offering a NetFlix-like service, in which each customer has a prioritized list of requested titles, perhaps several hundred per user. The operations I need to support efficiently are:

Show a user's queue, numbered by priority from 1 (first, highest priority) on up with consecutive integers.

Remove a title from the queue when it ships. All later titles move up in priority.

Allow users to add a title, delete a title, or change the priority of a title, and adjust all the priorities accordingly.

If I explicitly store the integer priority of each title, then I have constantly re-number hundreds of rows, whenever a title ships and leaves the queue or a user changes a title's priority.

I could represent the priorities within the database as floats or strings, and only number them when presenting them to the user. I can see an ornery corner-case where the user wants to prioritize a title between two that are already very close.

So experts, how would you do it? Is there an elegant solution? Thanks.