Linux 设计跟踪订单和愿望清单的数据库的最佳方法是什么？

What's the best approach to designing a database that keeps track of orders and wish lists?

提问by Mohamad

The best way to describe this scenario is to use an example. Consider Netflix: do they store their orders (DVD's they mail out) in a separate table from their member lists (NOT members table, but a joiner table of members and movies--a list of movies each member has created), or are orders distinguished by using additional information in the same row of the same table?

描述此场景的最佳方式是使用示例。考虑 Netflix：他们是否将他们的订单（他们邮寄的 DVD）与他们的成员列表（不是成员表，而是成员和电影的连接表——每个成员创建的电影列表）存储在一个单独的表中，或者是订单通过在同一个表的同一行中使用附加信息来区分？

For those not familiar with Netflix, imagine a service that lets you create a wish list of movies. This wish list is subsequently sent to you incrementally, say two movies at a time.

对于那些不熟悉 Netflix 的人，想象一个可以让您创建电影愿望清单的服务。此愿望清单随后会逐步发送给您，例如一次发送两部电影。

I would like to implement a similar idea using a MySQL database, but I am unsure whether to create two tables (one for orders and one for lists) and dynamically move items from the lists table to the orders table (this process should be semi-automatic based on the member returning an item, where before a new one is sent out, a table with some controls will be checked to see if the user is still eligible/has not gone over his monthly limit)...

我想使用 MySQL 数据库实现类似的想法，但我不确定是否创建两个表（一个用于订单，一个用于列表）并将项目从列表表动态移动到订单表（此过程应该是半自动基于会员返回项目，在发送新项目之前，将检查带有一些控件的表格，以查看用户是否仍然符合条件/没有超过他的每月限额）...

Thoughts and pros and cons would be fantastic!

想法和利弊会很棒！

EDIT: my current architecture is: member, items, members_items, what I am asking is if to store orders in the same table as members_items or create a separate table.

编辑：我当前的架构是：member、items、members_items，我要问的是是将订单与 members_items 存储在同一个表中还是创建一个单独的表。

采纳答案by C???

Moving things from one database table to another to change its status is simply bad practice. In a RDBMS, you relate rows from one table to other rows in other tables using primary and foreign key constraints.

将事物从一个数据库表移动到另一个数据库表以更改其状态是一种糟糕的做法。在 RDBMS 中，您可以使用主键和外键约束将一个表中的行与其他表中的其他行相关联。

As for your example, I see about four tables just to get started. Comparing this to Netflix, the grand-daddy of movie renting, is a far-cry from reality. Just keep that in mind.

至于你的例子，我看到大约四个表只是为了开始。将这与电影租赁的祖父Netflix相比，与现实相去甚远。要时刻铭记在心。

1. A Usertable to house your members.
2. A Movietable that knows about all of the available movies.
3. A Wishlistor Queuetable that has a one-to-many relationship between a User and Movies.
4. An Orderor Rentaltable that maps users to the movies that are currently at home.

1. 一个用户表来存放您的会员。
2. 一个电影表，它知道所有可用的电影。
3. 甲收藏或队列表具有用户和影之间的一对多的关系。
4. 将用户映射到当前在家中的电影的Order或Rental表。

Statuses of the movies in the Movie table could be in yet another table where you relate a User to a Movie to a MovieStatus or something, which brings your table count to 6. To really lay this out and design it properly you may end up with even more, but hopefully this sort of gives you an idea of where to begin.

Movie 表中电影的状态可以在另一个表中，在该表中您将用户与电影与 MovieStatus 或其他东西相关联，这使您的表数达到 6。要真正布局并正确设计它，您最终可能会得到甚至更多，但希望这种让你知道从哪里开始。

EDIT:Saw your update on exactly what you're looking for. I thought you were designing from scratch. The simple answer to your question is: have two tables. Wishlists (or member_items as you have them) and Orders (member_orders?) are fundamentally different so keeping them separated is my suggestion.

编辑：看到你的更新正是你正在寻找的。我以为你是从头开始设计的。你的问题的简单答案是：有两张桌子。愿望清单（或您拥有的 member_items）和订单（member_orders？）根本不同，因此我的建议是将它们分开。

回答by Petrogad

I feel like they would store their movies as follows (simplified of course):

我觉得他们会按如下方式存储他们的电影（当然是简化的）：

tables:

表：

• Titles
• Members
• Order
• Order_Has_Titles

• 标题
• 会员
• 命令
• Order_Has_Titles

This way an order which has a foreign key to the Members would then have a pivot table as many orders could have many titles apart of them.

这样，具有成员外键的订单将有一个数据透视表，因为许多订单可以有许多标题。

When you have a many to many realtionship in the database you then need to create a pivot table:

当您在数据库中有多对多关系时，您需要创建一个数据透视表：

Order_Has_Titles:
    ID (auto-inc)
    Order_FkId (int 11)
    Title_FkId (int 11)

This way you're able to put multiple movies apart of each order.

通过这种方式，您可以将每个订单中的多部电影分开。

Of course this is simplified, and you would have many other components which would be apart of it, however at a basic level, you can see it here.

当然，这是简化的，您将有许多其他组件可以作为它的一部分，但是在基本级别上，您可以在此处看到它。

回答by ChrisW

A problem with storing orders in the members table is that there's a variable number (0, 1, or several) of orders per member. The way to do this using a relational database is to have two separate tables.

在成员表中存储订单的一个问题是每个成员的订单数量是可变的（0、1 或几个）。使用关系数据库执行此操作的方法是拥有两个单独的表。