Let's say I have a user table and a card table, like this:
user: user_id
card: card_id user_id card_text card_order
As of right now, a user can have up to five cards. Users should also be able to re-arrange their cards on the frontend. So let's say a user had four cards, with the text for each one being "Apple", "Banana", "Carrot", "Donut", respectively.
The users should be able to re-arrange them and this change should be persisted into the database. So for example, an example schema where the order was Apple, Carrot, Donut, and Banana would look like this:
card_id user_id card_text card_order
------------------------------------------
23 1 Apple 1
53 1 Banana 4
43 1 Carrot 2
58 1 Donut 3
Is there a better way to do this? My issue is with updating them.
Let's say the user drags the Banana card to the #2 slot. Then Apple would stay 1, Banana would become 2, Carrot would become 3, and Donut would become 4. I'm not exactly sure what sort of SQL code I would have to execute to perform that sort of logic. Maybe something like..
UPDATE card SET card_order = card_order + 1 WHERE user_id = $1 AND card_order >= $2;
UPDATE card SET card_order = $2 WHERE user_id $1 AND card_id = $2;
Something like that? Not sure, but I feel like there is a better way?
An alternative is to have something like a card_order column in the user table which contains the ordering like: [ 23, 43, 58, 53 ], which may be easier to re-arrange, but then I have to do a JOIN with the user table every time I want to get the cards in the correct order. In addition, I'm not sure how well foreign keys would work in that situation? I fear the array could accidentally get corrupted with old card ids if the referential integrity was not checked.
