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.