数据库中有序列表的最佳表示? [英] Best representation of an ordered list in a database?

问题描述

我知道这种违反关系数据库的原则，但让我描述一下情况.

I know that this sort of goes against the principles of a relational database but let me describe the situation.

我有一个页面，用户将在其中放置许多项目.

I have a page where the user will place a number of items.

 ________________
| -Item1         |
| -Item2         |
| -Item3         |
| -Item4         |
|________________|

这些物品必须按照用户给它们的顺序排列.但是，用户可以随意更改此顺序.

These items have must stay in a the order the user gives them. However this order may be changed an arbitrary number of times by the user.

 ________________
| -Item1         |
| -Item4         |
| -Item2         |
| -Item3         |
|________________|

方法一

我最初的想法是给项目一个索引来代表它们在列表中的位置

My original thought was to give the items an index to represent thier place in the list

Page           Item
-----------    ---------------
FK | pid       FK | pid 
   | name      PK | iid 
                  | index
                  | content 

使用此解决方案，您可以选择项目 where pid = Page.pid 和 order by index 这很方便.但是，每次更改顺序时，您都必须在其他项目(最佳情况)和所有其他项目(最坏情况)之间的任何位置进行更改.

With this solution you can select items where pid = Page.pid and order by index which is convenient. However every time you change the order you have to change anywhere between one other item (best case) and all the other items (worst case).

方法二

我还考虑制作一个链表"之类的数据结构，其中每个项目都指向列表中的下一个项目.

I also considered making a "linked list" like data structure where each item points to the next item in the list.

Page           Item
-----------    ---------------
FK | pid       FK | pid 
   | name      PK | iid 
                  | next
                  | content 

这可能会降低更改订单的成本，但我们将不得不依赖前端编程来提取订单.

This potentially makes changing the order less expensive but we would have to rely on front end programming to extract the order.

有没有我没有想到的方法?请告诉我.

Is there an approach that I haven't thought of? Please let me know.

推荐答案

我认为 @a1ex07 在这里是正确的 (+1).我不认为 itemOrder 中的差距违反 3NF，但我确实担心 3NF 的不同违反(更多内容见下文).我们还必须注意 itemOrder 字段中的错误数据.这是我的开始方式:

I think @a1ex07 is on the right track here (+1). I don't think gaps in itemOrder violate 3NF, but I do worry about a different violation of 3NF (more on this below). We also have to watch out for bad data in the itemOrder field. Here's how I'd start:

create table pages (
  pid int,
  primary key (pid)
);

create table users (
  uid int,
  primary key (uid)
);

create table items (
  iid int,
  primary key (iid)
);

create table details (
  pid int not null references pages(pid),
  uid int not null references users(uid),
  iid int not null references items(iid), 
  itemOrder int,
  primary key (pid, uid, iid),
  unique (pid, uid, itemOrder)
);

主键确保对于每个页面，对于每个用户，都有唯一的项目.唯一约束确保对于每个页面，对于每个用户，都有唯一的 itemOrders.这是我对 3NF 的担忧:在这种情况下，itemOrder 并不完全依赖于主键；它仅取决于 (pid, uid) 部分.那甚至不是 2NF；这是一个问题.我们可以在主键中包含 itemOrder，但我担心它可能不是最小的，因为 PK 需要.我们可能需要将其分解为更多的表.仍然在想 ...

The primary key ensures that for each page, for each user, there are unique items. The unique constraint ensures that for each page, for each user, there are unique itemOrders. Here's my worry about 3NF: in this scenario, itemOrder is not fully dependent on the primary key; it depends only on the (pid, uid) parts. That's not even 2NF; and that's a problem. We could include itemOrder in the primary key, but then I worry that it might not be minimal, as PKs need to be. We might need to decompose this into more tables. Still thinking . . .

[ 编辑 - 关于该主题的更多思考...]

[ EDIT - More thinking on the topic . . . ]

假设

1. 有用户.

1. There are users.

有页面.

有项目.

(page, user) 标识一组项目.

(page, user) identifies a SET of items.

(page, user) 标识一个有序的槽位列表，如果我们愿意，我们可以在其中存储项目.

(page, user) identifies an ordered LIST of slots in which we can store items if we like.

我们不希望(页面，用户)的列表中有重复的项目.

We do not wish to have duplicate items in a (page,user)'s list.

A 计划

删除上面的 details 表.

添加一个表，ItemsByPageAndUser，来表示由(页面，用户)标识的项目集.

Add a table, ItemsByPageAndUser, to represent the SET of items identified by (page, user).

create table ItemsByPageAndUser (
   pid int not null references pages(pid),
   uid int not null references users(uid),
   iid int not null references items(iid),
  primary key (pid, uid, iid)   
)

添加表 SlotsByPageAndUser，以表示可能包含项目的插槽的有序 LIST.

Add table, SlotsByPageAndUser, to represent the ordered LIST of slots that might contain items.

create table SlotsByPageAndUser (
   pid       int not null references pages(pid),
   uid       int not null references users(uid),
   slotNum   int not null,
   iidInSlot int          references items(iid),
 primary key (pid, uid, slotNum),   
 foreign key (pid, uid, iid) references ItemsByPageAndUser(pid, uid, iid),
 unique (pid, uid, iid)
)

注意 1:iidInSlot 可以为空，因此我们可以根据需要拥有空槽.但是，如果存在项目，则必须对照项目表进行检查.

Note 1: iidInSlot is nullable so that we can have empty slots if we want to. But if there is an item present it has to be checked against the items table.

注意 2:我们需要最后一个 FK 以确保我们不会添加任何不在此(用户、页面)可能项目集中的项目.

Note 2: We need the last FK to ensure that we don't add any items that are not in the set of possible items for this (user,page).

注意 3:(pid, uid, iid) 上的唯一约束强制执行我们的设计目标，即在列表中具有唯一项(假设 6).如果没有这个，我们可以从 (page,user) 标识的集合中添加任意数量的项目，只要它们位于不同的插槽中即可.

Note 3: The unique constraint on (pid, uid, iid) enforces our design goal of having unique items in the list (assumption 6). Without this we could add as many items from the set identified by (page,user) as we like so long as they are in different slots.

现在我们已经很好地将项目与其插槽分离，同时保留了它们对(页面、用户)的共同依赖.

Now we have nicely decoupled the items from their slots while preserving their common dependence on (page, user).

这个设计肯定是 3NF 并且可能是 BCNF，尽管我担心 SlotsByPageAndUser 在这方面.

This design is certainly in 3NF and might be in BCNF, though I worry about SlotsByPageAndUser in that regard.

问题是由于表SlotsByPageAndUser 中的唯一约束，SlotsByPageAndUser 和ItemsByPageAndUser 之间关系的基数是一对一的一.通常，不是实体子类型的 1-1 关系是错误的.当然也有例外，也许这就是其中之一.但也许有更好的方法...

The problem is that because of the unique constraint in table SlotsByPageAndUser the cardinality of the relationship between SlotsByPageAndUser and ItemsByPageAndUser is one-to-one. In general, 1-1 relationships that are not entity subtypes are wrong. There are exceptions, of course, and maybe this is one. But maybe there's an even better way . . .

B 计划

1. 删除 SlotsByPageAndUser 表.

将 slotNum 列添加到 ItemsByPageAndUser.

将 (pid, uid, iid) 上的唯一约束添加到 ItemsByPageAndUser.

Add a unique constraint on (pid, uid, iid) to ItemsByPageAndUser.

现在是:

create table ItemsByPageAndUser (
   pid     int not null references pages(pid),
   uid     int not null references users(uid),
   iid     int not null references items(iid),
   slotNum int,
 primary key (pid, uid, iid),   
 unique (pid, uid, slotNum)
)

注意 4:让 slotNum 为空可以保留我们在集合中指定不在列表中的项目的能力.但 ...

Note 4: Leaving slotNum nullable preserves our ability to specify items in the set that are not in the list. But . . .

注意 5:对涉及可为空列的表达式施加唯一约束可能会在某些数据库中导致有趣"的结果.我认为它会按照我们在 Postgres 中的预期工作.(请参阅关于 SO 的此讨论此处.)对于其他数据库，您的里程可能会有所不同.

Note 5: Putting a unique constraint on a expression involving a nullable column might cause "interesting" results in some databases. I think it will work as we intend it to in Postgres. (See this discussion here on SO.) For other databases, your mileage may vary.

现在没有乱七八糟的 1-1 关系了，这样更好.它仍然是 3NF，因为唯一的非键属性 (slotNum) 依赖于密钥、整个密钥，并且只依赖于密钥.(你不能不告诉我你在谈论什么页面、用户和项目.)

Now there is no messy 1-1 relationship hanging around, so that's better. It's still 3NF as the only non-key attribute (slotNum) depends on the key, the whole key, and nothing but the key. (You can't ask about slotNum without telling me what page, user, and item you are talking about.)

这不是 BCNF 因为 [ (pid, uid, iid) -> slotNum ] 和 [(pid,uid,slotNum) -> iid ].但这就是为什么我们对 (pid, uid, slotNum) 有唯一约束，以防止数据进入不一致状态.

It's not BCNF because [ (pid, uid, iid) -> slotNum ] and [(pid,uid,slotNum) -> iid ]. But that's why we have the unique constraint on (pid, uid, slotNum) which prevents the data from getting into an inconsistent state.

我认为这是一个可行的解决方案.

I think this is a workable solution.

这篇关于数据库中有序列表的最佳表示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！