How can I develop a database schema for a one to many relationship?
Your structure isn't one-to-many or many-to-many, it's just one-to-"blob:of:colon:separated: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.
You would have three tables in this situation:
CART (transaction header)---------------tran_id NUMBERfirst_name VARCHAR(100)last_name VARCHAR(100)CART_ITEM (transaction detail)---------------tran_id NUMBERitem_id NUMBER.. other details about this line item (quantity, etc)ITEM---------------item_id NUMBERname 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 = 1ORDER BY i.name