Postgres card order database schema?

Question

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.

Answer 1

I recommend using a double precision or numeric for card_order. Then you can easily move a card somewhere else without the need to update all other cards: just set card_order to the arithmetic mean of the card_orders of the cards between which the card should move.

• Ryan Peschel: Couldn't this eventually have precision issues leading to false orders as the means converge?
• Laurenz Albe: If you have that many rearrangements, use numeric. That has almost unlimited precision.