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:
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
...
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.
+3
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
---------------
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.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
+7
source to share