单个MySQL行中的多个项目？

Using MySQL + PHP, I want to store several food_items for a single restaurant order in a MySQL database.

使用MySQL + PHP,我想在MySQL数据库中为单个餐厅订单存储几个food_items。

I have two tables: orders & food_items:

我有两个表:orders&food_items:

Many food_item_ids will be stored for a single order.

许多food_item_ids将存储为单个订单。

I'm just wondering, what's the best approach to storing the food_item_ids in the orders_table?

我只是想知道,在orders_table中存储food_item_ids的最佳方法是什么?

Should I store each food_item_id in a separate row of the orders table, or place all of a particular order's food_item_ids into a single row?

我应该将每个food_item_id存储在订单表的单独行中,还是将特定订单的所有food_item_id放入一行?

2 个解决方案

#1

If you want 3NF (and you should unless and until you find performance is a problem), you should either:

如果你想要3NF(你应该除非并且直到你发现性能有问题),你应该:

store the order ID in each row of the food_item table (if food_items is a per-order table);

将订单ID存储在food_item表的每一行中(如果food_items是按订单表);

or use a many-to-many relationship (if food_items is just the possible things you can order).

或使用多对多关系(如果food_items只是您可以订购的东西)。

With the first option, something like this would suffice:

使用第一个选项,这样的东西就足够了:

Orders:
    order_id
    other stuff
FoodItems:
    order_id
    item_id
    other stuff.

For the second option, a separate table provides the many-to-many relationship:

对于第二个选项,单独的表提供了多对多关系:

Orders:
    order_id
    other stuff
FoodItems:
    item_id
    other stuff.
FoodItemsInOrder:
    order_id
    item_id
    count

In my opinion, all database tables should be designed in third-normal form. Once you table gets so big that performance may become an issue (and it will have to be very big), then you can start thinking about de-normalizing for speed.

在我看来,所有数据库表都应该以第三范式设计。一旦你的表变得如此之大以至于性能可能成为一个问题(它必须非常大),那么你就可以开始考虑对速度进行去标准化。

For a restaurant, I cannot imagine the order and food item tables getting anywhere near big enough to warrant de-normalizing.

对于一家餐馆来说,我无法想象订单和食品项目表的大小足以保证去标准化。

#2

If each food item is unique to a particular order, then you should store the order ID in the food_items table, then you could query like this:

如果每个食品对特定订单都是唯一的,那么您应该将订单ID存储在food_items表中,然后您可以这样查询:

SELECT orders.id, food.id
FROM orders
INNER JOIN food ON orders.id = food.order_id

However if you have standard food items, e.g. "burger", "chips", "hot dog" then you should use three tables. One for orders, one for food items, and one to join the two:

但是,如果您有标准食品,例如“汉堡”,“筹码”,“热狗”那么你应该使用三张桌子。一个用于订单,一个用于食品,一个用于加入两个:

SELECT orders.id, food.id
FROM orders
INNER JOIN orders_food ON orders.id = orders_food.order_id
INNER JOIN food ON orders_food.food_id = food.id

#1