performance - What is the best way to store grid locations in the database? -

i have grid derived http://gridster.net/.

• columns of grid seen different type[s] of entity.
• rows seen priority.

each cell object this

entity

• id
• name
• priority
• typeid

note: priority , typeid both ints relate grid/column location when generating grid.

my problem: every time cell moved around need update typeid , priority of cell detect other cells might have moved around.

normally means mass update on cells below priority + 1 (since has moved down).

this solution works, decouple design , maybe table in database responsible keep track of priority. (it doesn't need related grid id, should able order priority) sort of other table responsible order/priority. if have grid , show entity entities.

i thought approach of linked list good. keep track of whats above , below, (previous & next) difficult run queries on.

any ideas?

i had exact same question.

the big difference me not how store data best way send modified data.

instantiate gridster , add drop event

$(".gridster ul").gridster({         draggable: {             stop: function(event, ui) {                 window.settimeout(savepositions, 200); //short timeout allow dom update             }         }     }); 

so default gridster looks this:

<div class="gridster ready">       <ul style="height: 480px; position: relative;">         <li data-row="1" data-col="1" data-sizex="2" data-sizey="1" class="gs_w"></li>         <li data-row="3" data-col="1" data-sizex="1" data-sizey="1" class="gs_w"></li>          <li data-row="3" data-col="2" data-sizex="2" data-sizey="1" class="gs_w"></li>         <li data-row="1" data-col="3" data-sizex="2" data-sizey="2" class="gs_w"></li>       </ul>     </div> 

detecting changes can problematic send data grid elements in case moved unintentionally.

function savepositions() {     var gridelements = [];     $(".gridster > ul > .gs_w").each(function() {         gridelements.push({              id: $(this).data("id"),              priority: $(this).data("row"),              typeid: $(this).data("col")          });     });      var data = { gadgets: gridelements };     $.ajax({         type: 'post',         url: "<whatever>",         data: data,         contenttype: "application/json; charset=utf-8",         datatype: "json"     }); } 

once in application, can manipulate , store these like. used mongodb allowed me to, say, perform filtering , ordering on data. while doesn't decouple design, clean , dynamic solution. when stored using mongodb, looked this:

{     id: "83cd82f7-d024-4994-9f8d-0595472398e6",     gadgets: [         {             id: "8592c6b7-2b39-4b5c-ac08-0666b9fd6152",             priority: 1,              typeid: 1         },         {             id: "8592c6b7-2b39-4b5c-ac08-0666b9fd6152",             priority: 1,              typeid: 2         },         {             id: "8592c6b7-2b39-4b5c-ac08-0666b9fd6152",             priority: 1,              typeid: 3         }     ] } 

if want apply filter or order, you'll need method of rearranging items rows , columns. biggest problem faced adding, ordering or removing grid , having other items change positions. avoid usage of linked lists in case too, while may able visualize columns linked lists, grid elements can interact other columns , important able represent/capture.

for example: in image, if filtered out items priority of "1", leave large gaps @ top of page before first elements since rows , columns saved database.

my approach problem involved 2-dimensional array representation of locations , sizes.

i hope i've been helpful?