代码之家 › 专栏 › 技术社区 › Brandon Yarbrough

用户可排序记录

database-design database

Brandon Yarbrough · 技术社区 · 15 年前

对于我的webapp中的每个用户,有 n 相关小工具。每个小部件都在数据库的小部件表中表示。用户可以对他们的小部件进行排序,他们永远不会拥有超过几十个小部件,而且他们经常对小部件进行排序。

我并没有经常处理对它们具有固有顺序的数据库项。订购它们的好策略是什么?起初,我以为一个简单的“sortindex”列可以正常工作,但后来我开始想如何初始化这个值。它可能必须是一个唯一的值,并且应该大于或小于所有其他排序索引。不过,我不想每次创建新的小部件时都检查该用户的所有其他排序索引。这似乎是不必要的。

也许我可以有一个默认的“底部优先”排序索引?但是我该如何区分它们呢?我想我可以使用一个创建日期标志,但是如果一个用户想要在所有底层优先级的小部件中间插入一个小部件呢?

处理这类事情的标准方法是什么?

4 回复 | 直到 15 年前

Quassnoi 15 年前

用户可编辑排序的最佳方法是 id 在A linked list :

user_id   widget_id  prev_widget_id
   ----        ----            ----
      1           1               0
      1           2               8
      1           3               7
      1           7               1
      1           8               3
      2           3               0
      2           2               3

这将使 5 小部件 user 1 按此顺序: 1, 7, 3, 8, 2 ;和 2 小部件 user 2 按此顺序: 3, 2

你应该做 UNIQUE 索引 (user_id, widget_id) 和 (user_id, prev_widget_id) .

要按预期顺序获取小部件,可以这样查询,例如 Oracle :

SELECT  w.*
FROM    (
        SELECT  widget_id, level AS widget_order
        FROM    widget_orders
        START WITH
                user_id = :myuser
                AND prev_widget_id = 0
        CONNECT BY
                user_id = PRIOR user_id
                AND prev_widget_id = PRIOR widget_id
        ) o
JOIN    widgets w
ON      w.widget_id = o.widget_id
ORDER BY
        widget_order

要更新订单,最多需要更新 3 行(即使移动整个小部件块)。

SQL Server 和 PostgreSQL 8.4 使用递归实现此功能 CTE S:

WITH    
-- RECURSIVE
-- uncomment the previous line in PostgreSQL
         q AS
         (
         SELECT  widget_id, prev_widget_id, 1 AS widget_order
         FROM    widget_orders
         WHERE   user_id = @user_id
         UNION ALL
         SELECT  wo.widget_id, wo.prev_widget_id, q.widget_order + 1
         FROM    q
         JOIN    wo.widget_orders wo
         ON      wo.user_id = @user_id
                 AND wo.prev_widget_id = q.widget_id
        )
SELECT  w.*
FROM    q
JOIN    widgets w
ON      w.widget_id = q.widget_id
ORDER BY
        widget_order

请参阅我的博客中的这篇文章,了解如何在 MySQL :

Sorting lists

Eric 15 年前

如果您让用户根据自己的喜好对小部件进行排序,那么您需要创建一个查找表,如下所示:

create table widgets_sorting
(
    SortID int primary key,
    UserID int,
    WidgetID int,
    SortIndex int
)

然后,对用户的小部件进行排序:

select
    w.*
from
    widgets w
    inner join widgets_sorting s on
        w.WidgetID = s.WidgetID
    inner join users u on
        s.UserID = u.UserID
order by
    s.SortIndex asc

这样,您只需为新用户添加新行到 widgets_sorting 表。确保在widgetid和userid列上放置了一个外键约束和一个索引。

这些查找表实际上是解决这种个性化列表中常见的多对多关系的最佳方法。希望这能为你指明正确的方向!

Jamie Rumbelow 15 年前

我喜欢使用双表方法——这可能有点令人困惑,但如果您使用的是像ActiveRecord这样的ORM,这很容易,而且如果您编写了一些巧妙的代码,它也可以管理。

使用一个表将用户链接到排序,使用一个表链接小部件和位置以及排序。这样,事情就更清楚了,您可以使用SQL联接或单独的查询从各个表中提取各种数据。您的结构应该如下所示:

//Standard user + widgets table, make sure they both have unique IDs
CREATE TABLE users;
CREATE TABLE widgets;

//The sorting tables
CREATE TABLE sortings (
    id INT, //autoincrement etc,
    user_id INT
)

CREATE TABLE sorting_positions (
    sorting_id INT,
    widget_id INT,
    position INT
)

希望这是有意义的,如果您仍然困惑,请对这个消息进行评论,我将为您编写一些基本代码。

杰米

Jay 15 年前

如果您的意思是每个用户为小部件分配自己的排序顺序,那么Eric的回答是正确的。假设您随后必须给用户一种分配排序值的方法。但是如果这个数字如你所说的一般,那么你可以给他一个屏幕,列出所有的小部件,或者让他输入订单号,或者按顺序显示它们,并在每个小部件旁边放置上下按钮,如果你想成为一个花花公子,给他一个拖放的方法。

如果所有用户的订单都是相同的,那么问题是,这个订单来自哪里?如果它是任意的,只需在创建新的小部件时分配一个序列号。