How can I design a database schema for relationships to each other?

I need to implement a shopping cart. Let's say I sell 20 different items.

I have some simple tables:

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

item_id    name         price
1          Ruby         $1.00
2          Diamond      $2.00


The point is how I can query the cart and display items in an order other than 1: 4: 15: 16 format, but rather like Ruby, Amethyst, Opal, Silver.


source to share

1 answer

Your structure is not one-to-many or many-to-many, it is just one-to-block: from: colon: delimited: text ".

A correct multivalued relationship usually uses a table to link the relationship. In your particular case, this table is often the Transaction Details table, while your Basket table is the Transaction Header table.

In this situation, you will have three tables:

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_id NUMBER
name  VARCHAR(100)
price  NUMBER(18,4)


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

SELECT h.tran_id,, 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




All Articles