如何为1到多个关系开发数据库模式?

I need to implement a shopping cart. Let's assume I am selling 20 different items.

我需要实现一个购物车。假设我卖了20种不同的商品。

I have a couple simple tables:

我有几个简单的表格:

Cart
-------
tran_id    first_name    last_name    items
1          John          Doe          1:4:15:16


Item
-------
item_id    name         price
1          Ruby         $1.00
2          Diamond      $2.00
...

Point is, how can I query the cart and display the items in an order not in the 1:4:15:16 format but rather as Ruby, Amethyst, Opal, Silver.

要点是，我如何查询购物车并显示订单中的项目，而不是在1:4:15:16的格式，而是作为Ruby, Amethyst, Opal, Silver。

1 个解决方案

#1

Your structure isn't one-to-many or many-to-many, it's just one-to-"blob:of:colon:separated:text".

你的结构不是一对多或多对多，它只是一个对“blob:of::: apart:text”。

A proper many-many relationship usually uses a table to bridge the relationship. In your particular case, that table is often the "transaction detail" table, while your "cart" table is the "transaction header" table.

一个适当的多-多关系通常使用一个表来连接关系。在特定情况下，该表通常是“事务细节”表，而“cart”表是“事务头”表。

You would have three tables in this situation:

在这种情况下，你会有三张桌子:

CART (transaction header)
---------------
tran_id NUMBER
first_name VARCHAR(100)
last_name VARCHAR(100)

CART_ITEM (transaction detail)
---------------
tran_id NUMBER
item_id NUMBER
.. other details about this line item (quantity, etc)

ITEM
---------------
item_id NUMBER
name  VARCHAR(100)
price  NUMBER(18,4)

Then, to query this to get what you are looking for, just say:

然后，要查询这个以得到您想要的，只需说:

SELECT h.tran_id, i.name, i.price
  FROM cart h 
INNER JOIN cart_item d ON (h.tran_id = d.tran_id)
INNER JOIN item i ON (d.item_id = i.item_id)
WHERE h.tran_id = 1
ORDER BY i.name

#1