Looking for the best approach (Setting an order)

[tryingtolearn]

I am looking for some advice on the best way to maintain an options system.

I have a list of items (In a mysql table) that make up options or packages that can be ordered

 

For example

Combo 1 comes with

-List Item Orange

-List Item Grape

-List Item Brick

 

Combo 2 comes with

-List Item Grape

-List Item Salmon

-List Item Tiger

 

Combo 3 comes with

-List Item Orange

-List Item Brick

-List Item House

-List Item Red

-List Item Blue

-List Item Car

-List Item Boat

-List Item Plane

 

etc...

 

The issue is that the list items have to be displayed in a specific  order and new list items are added on regular intervals and others are deleted.

I was going to add a column to the DB with a # order so I could just ORDER the result based off that but say there are 30 items and I need to add a new item in position 2 everything below it needs to be renumbered.

 

Is there a better approach to this system? Any advice is appreciated, thanks

 

 

[vinny42]

 

 but say there are 30 items and I need to add a new item in position 2 everything below it needs to be renumbered.

 

True, but that is one query:

 

UPDATE table SET order_column = order_column+1 WHERE order_column >= X;

where X is the order_column value of the first record that needs to be moved. 

And you could even stick that in a trigger and not have to worry about it ever again. :-)

 

You should perhaps use a separate table for this data, the sortorder itself is not part of the daa being sorted and if you ever decide to sort the same items differently in two dropdowns (of wherever) you don't want to have to add a new column every time.

[AbraCadaver]

Maybe a better way, but you could have a large increment in the order numbers, like 100, 200, 300, etc...

[tryingtolearn]

Thanks

Kinda what I was thinking just wanted put the feelers out to see if I was overlooking the obvious (Known to do that every once in a while).

Like the update as well.

Thanks for taking the time.

[vinny42]

 

Maybe a better way, but you could have a large increment in the order numbers, like 100, 200, 300, etc...

 

You could do that; make large gaps and put new numbers right in between. inserting between 100 and 200 yould be 150, between 100 and 150 at 125 etc. But in the end that's just postponing the envitable, you will have to renumber some time.