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.