Luke Xu Luke Xu - 1 month ago 8
MySQL Question

Most efficient way to store a list with sections that can be drag and dropped in MySQL

I'm looking to implement something like

-- Section 1 --
-- Line Item 1 of Section 1 --
-- Line Item 2 of Section 1 --
-- Line Item 3 of Section 1 --
-- Section 2 --
-- Line Item 1 of Section 2 --
-- Line Item 2 of Section 2 --
-- Line Item 3 of Section 2 --
-- Section 3 --
-- Line Item 1 of Section 3 --
-- Line Item 2 of Section 3 --
-- Line Item 3 of Section 3 --


Both sections and line items can be drag and dropped. So for example, moving a section would turn the table into something like so

-- Section 1 --
-- Line Item 1 of Section 1 --
-- Line Item 2 of Section 1 --
-- Line Item 3 of Section 1 --
-- Line Item 4 of Section 1 --
-- Line Item 5 of Section 1 --
-- Line Item 6 of Section 1 --
-- Section 3 --
-- Line Item 1 of Section 3 --
-- Line Item 2 of Section 3 --
-- Section 2 --
-- Line Item 1 of Section 2 --


I'm interested in the theory behind it right not so much for an actual code implementation. I was thinking something a long the lines of having three tables

TABLE ONE - line items - All the line items and all relative information with a fk to a section table and a ordinal table

TABLE TWO - sections - A section table, used for queries of additional information (such as the total sum of a column within a section)

TABLE THREE - ordinals - An ordinal table that stores the absolute ordering of where the line items are stored.

The reason why I don't like this approach is that both the sections table and ordinals table have to always stay in sync. By changing the ordinals around, you could move the line items to appear in different sections but if forgot to update the section table and tried to query by section, you'd get the wrong sum.

Answer

You dont need TABLE THREE just add the ordinal to TABLE ONE and to TABLE TWO

In your UI you allow move around items between section and order. Then you have to work in your saving process.

DELETE Sections 
INSERT Sections
DELETE items 
INSERT items
UPDATE Section_fk for items
UPDATE ordinal_id for items
UPDATE ordinal_id for Section