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.

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.