在数据库中存储有序列表的最佳方法？ [英] Best way to store ordered lists in a database?

问题描述

在数据库中存储有序列表的最佳方法是什么？可以轻松地更新它们（添加，删除和更改条目顺序）？

考虑一个数据库，你有一个用户和电影的表。每个用户都有喜欢的电影列表。

由于许多用户可以喜欢同一部电影，因此我将用户和电影分开放置，并使用第三个表来连接他们，usermovies

usermovies包含用户和电影的ID以及订单号。订单号用于为用户订购电影列表。

例如，用户Josh可能具有以下列表：

1. 普罗米修斯


2. 黑人男子


3. 独裁者

，用户Jack可能会有以下列表：

1. 独裁者


2. 普罗米修斯


3. 战舰


4. 白雪公主和猎人

所以，他们分享一些收藏，但不一定是相同的顺序。

我可以得到每个电影ID的列表用户使用查询：

  SELECT movie_id FROM usermovies WHERE user_id =？ ORDER BY order_number 
  

然后，使用有序的movie_ids，我可以使用另一个查询获取电影列表< /？

  SELECT name FROM movies WHERE id in（？，？，？）ORDER BY FIELD（id，？，？，？）
  

所以查询工作，但更新列表似乎现在很复杂 - 有更好的方法来存储这些信息获取用户x的电影列表很容易，添加电影，删除它们并更改列表的顺序？

解决方案

具有附加列的连接/链接表，用于电影和用户之间关联的属性是实现与关联类的许多关联的标准方法 - 所以您所做的工作似乎是正确的。

关于插入/更新/删除的简易性，每次执行插入/更新/删除时，都必须管理整个关联（用户影片FK的所有行）。
这可能不是一个神奇/简单的方法。说到这一点，你还需要在一个事务中运行这些操作，更重要的是在这个连接表上有一个'version'列，如果你的应用程序是多用户能力。

What's the best way to store "ordered lists" in a database, so that updating them (adding, removing and changing the order of entries) is easily done?

Consider a database where you have a table for users and movies. Each user has a list of favorite movies.

Since many users can like the same movie, I made users and movies separate tables and uses a third table to connect them, usermovies.

usermovies contains an id of a user and a movie and an "order number". The order number is used to order the list of movies for users.

For example, user Josh might have the following list:

1. Prometheus
2. Men in Black 3
3. The Dictator

and user Jack might have a list like:

1. The Dictator
2. Prometheus
3. Battleship
4. Snow White and the Huntsman

So, they share some favorites, but not necessarily in the same order.

I can get the list of movie IDs for each user using a query:

SELECT movie_id FROM usermovies WHERE user_id =? ORDER BY order_number

Then, with the ordered movie_ids, I can get the list of movies using another query

SELECT name FROM movies WHERE id in (?,?,?) ORDER BY FIELD (id, ?,?,?)

So queries work, but updating the lists seems really complex now - are there better ways to store this information so that it would be easy to get the list of movies for user x, add movies, remove them and change the order of the list?

解决方案

A junction/link table with additional columns for the attributes of the association between movies and users is the standard way of realizing a many-many association with an association class - so what you have done seems correct.

Regarding the ease of insert/update/delete, you'll have to manage the entire association (all rows for the user-movie FKs) every time you perform an insert/update/delete. There probably isn't a magical/simpler way to do this.

Having said this, you'll also need to run these operations in a transaction and more importantly have a 'version' column on this junction table if your application is multi-user capable.

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