如何保持数据库表中的记录排序 [英] How to keep ordering of records in a database table

问题描述

我创建了一个数据库表，用于存储最终显示在网页上的菜单链接。

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

我的问题是，我想控制菜单项的顺序。我可以有一个称为订单的字段，但每次我有一个新的菜单链接，我必须插入订单，并更改所有的记录更高的顺序+1。

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  

我可以有一个名为 MenuLinks 的表格，并且具有以下列： Name，Order

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

我的资料将如下所示：

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.

推荐答案

我认为这与使用数据库表的一般问题有关一个数组与一个链表。
如何存储引用同一个表中的下一条记录的外键？这是链接列表的方法。

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

订单将为1> 5> 2> 3> 4由下一列确定。
您也可以使用prev列，使其类似于双向链表。

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.

这篇关于如何保持数据库表中的记录排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！