I'm making a database that has to store user details (table: users), item details (table:items) and i need to allow each user to choose up to 100 items each.
I could make a table that references user_id and item_id, but i have another requirement. Each user needs to be able to change the order of the items on their list, not alphabetically or by date added, but any order they wish.
At first i though of adding a 'position' field, but things get messy when the user wants to move items around because every change would require two queries, and if for any reason one query didn't happen, the db would be broken.
Any ideas guys? I'd appreciate any input you can give me.