1

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be great.

However base on my research thats not possible. the consensus is just use a junction join table.

I can certainly make join table work, however I still need to preserve the order of those items. I know that I could add another column on the join table called 'item order'. But with that solution, it means each rearrangement would require n times of updates for its related items in the join table. The performance cost seems pretty big. However, I'm pretty new to sql, not sure the scale of things or that there might be other solution I might've missed? Would love to hear a better solution.

and I guess without saying, if we go in with plain array on todo table, drop the join table, lose referential integrity and cascade on delete, it would not a good compromise for performance.

Improve this question
3
  • How many todos and items per todo are we talking? What operations are expected to be frequent / rare? Concurrency? What needs to be fast? What needs to be bullet-proof?
    – Erwin Brandstetter
    Jan 21, 2022 at 4:55
  • I'm thinking in the magnitude of 1-20 items per todo. And I do not have a set limit on number of todo, but I think I should set a hard cap per user. As for the operation, I don't know the limitation of the database thus the design is not concrete. I guess this is where I hit the wall on deciding what can I allow users to do. Currently, reordering updates may happen a lot due to the autosaving system in place sending updates once 30 seconds after last modified. I'm not sure how to assign what needs to be bulletproof. sorry for late reply, and my amateur response.
    – Qi luo
    Feb 1, 2022 at 20:03
  • @ErwinBrandstetter I think if it does become very taxing on the database as it scales. I could implement things such as only save order when user publishes post, or explicitly saves order. And allow saving the order on the client through something such as indexDB. but i guess there would be additional logic to validate items if they were deleted in a different client.
    – Qi luo
    Feb 1, 2022 at 20:07

1 Answer 1

Reset to default
0

Don't use an array, use a junction table.

First, as you found out, you cannot have referential integrity with arrays. Also, rearranging a large array is expensive. There are good reasons why the first normal form outlaws composite attributes.

To model the ordering, add a double precision column to the junction table. That is better than an integral column, because you can add a value in the middle without reshuffling the whole set.

Improve this answer
3
  • Thank you, wow that seems like a clever trick. So I can insert and delete without reshuffling - If I reorder, I would swap their values ?
    – Qi luo
    Feb 1, 2022 at 20:15
  • @Qiluo: Change the value in one row to reorder two rows. The number of updated rows drives the cost.
    – Erwin Brandstetter
    Feb 1, 2022 at 21:02
  • There are more bigints than there are doubles, it just comes down to a wise choice of initial values. it's still possible to paint oneself into a corner after 64 or so steps.
    – Jasen
    Jun 11, 2022 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.