How to keep ordering of records in a database table

Question

i am creating a database table that is going to store menu links that will ultimately show up on a web page.

My issue is that i want to control the order of the menu items. I could have a field called order but everytime i have a new menu link i would have to insert the order and change all of the records with higher order to +1.

For example, lets say i want the links ( in this order):

Home  
About  
Products  
Shopping  

i could have a table called MenuLinks and have the columns: Name, Order

my data would look like this:

Name      Order  
Home      1  
About     2  
Products  3  
Shopping  4  

but if i wanted to now add a new link called ContactUs but i wanted to show up right under home.

can anyone think of a better way to store a list that requires ordering in a database table without this tedious maintenance effort.

Falcongentle

Answer 1

I feel this is related to the general problem of using an array vs a linked list. How about storing a foreign key referencing the next record in the same table? This is the linked list like approach.

For your example there are not too many tabs so an array based approach should work fine. But for someone having hundreds of records it may be useful to use a self-referential foreign key.

ID Name      NExT  
 1 Home      2  
 2 About     3  
 3 Products  4 
 4 Shopping  NULL

Adding and deleting rows will be akin to linked list insertion and deletion.

Update: Modified table

ID Name       NExT  
 1 Home       5  
 2 About      3  
 3 Products   4 
 4 Shopping   NULL
 5 Contact us 2

The order will be 1 > 5 > 2 > 3 > 4 as determined by the next column. You can also use a prev column which will make it similar to a doubly linked list.

Anglicize

the issue here is that it doesn't solve the problem. making one change requires you to update all of the records > that row – Falcongentle

No one change does not require you to update all rows. If you add Contact Us after Home then the table would like as shown. Updated the answer. – Anglicize

but you are updating the Next column in each record . . i dont understand why you are saying you are not updating all rows – Falcongentle

No I am not updating all the records. Notice that only the rows with ID 1 and 5 are updated in the example. The rows 2,About,3, 3, Products,4 and 4,Shopping,NULL are not updated in the example above. Think in terms of linked list and array insert. – Anglicize

How can you write a query that orders the table properly? It seems there isn't a simple one (one that isn't recursive). – Dishonorable

Yes, @RohitBanga, go ahead and try to sort this :) – Lecialecithin

And how would you sort something that has no order? you could only write a new function that looped your list and re ordered based on the above... but this would be too expensive for large lists – Adnah

Answer 2

Without an ORDER BY, you can't guarantee the order of the data - typically, without an ORDER BY it will be based on insertion order.

Sadly, there's no convention that works well for a user customizable sort order.
One could get away with using analytic/windowing/ranking functions like ROW_NUMBER, but it depends on data and database support (MySQL doesn't support analytic functions, Oracle 9i+/PostgreSQL 8.4+/SQL Server 2005+ do). But analytic functions don't help if you want an entry starting with "B" to appear before "A"/etc.

Your options are to either use two statements to insert a single record:

UPDATE YOUR_TABLE
   SET sort_order = sort_order + 1
 WHERE sort_order >= 2

INSERT INTO YOUR_TABLE
  (value, sort_order)
VALUES('new value', 2)

...or delete the existing records, and re-insert the list in the new order.

Putt

Answer 3

you should consider that when you use a linked list then when you want to reorder one of the items then you have to update other records as well and this needs to be done in a transaction which is not fast at all.(you need transaction because all the updates must be done completely or none of them must be updated)
there is an other solution for this problem that works on small lists.
to use this method you give each of your records a number. for example:

Name    Number
Home     5
About    10
Products 15
shopping 20

rows with smaller Number are at the begginnig of the list and the row with the biggest number will be the last item of your list now here is the trick, if you wanted to reorder the Products row and insert it between the Home and About all you have to do is to change the Number field of your Product to be equal to the number between Home and About Number
the Home number is 5 and the About number is 10 so the Number field of Product will be (5+10)/2 = 7.5

Name    Number
Home     5
About    10
Products 7.5
shopping 20

and now you can sort the final list, based on the Number field

Paddle

Answer 4

A better solution is to create an array of your elements IDs in order and turn that into a json array and store it as it is into a file or a table or wherever you want. You can then fetch your objects from the database and map your array back to a list of elements. You keep on one side the order and, on the other, the data. if you remove an element from the database you can remove elements from your array that map to nothing. If you add new element to the database you need to add them to your array as well but if you don't, nothing gets broken.

long story short:

1. Save the order as a json array of ids into a file or a table or wherever you want.

2. Save your elements in your database without worrying about their order.

3.When you need, get back your array of ids and map them to their respective element from the database.

Ambassador

Answer 5

You can add 2 columns (after,before) For example contact us is after home and before about, if you want add a new link between contact us and home you just need to change 3 records. Homes before column changes to (new link name), Then you insert new link and then contact us after column changes to (new link name). Sorry im not good in English.

Snazzy