I would like to make a shopingcart on my website and when you press buy it stores the things in your shoppingcart in a list of the id’s from the products. I want to safe the list in an mysql table with an id for the cart. What‘s the best type for that?
>Solution :
You’re talking about a one-to-many relation, so don’t store an array of integers in your relational database model.
Instead, you should create a second record type (table) named cart_items or similar, which holds the information about the items and is then related to the cart itself via a foreign key. This allows you to also track the quantity of items in the cart and additional information, for instance:
tbl_cart:
| id | user_id | created | modified |
|---|---|---|---|
| 1 | 1 | 2022-02-08 12:20:20 | NULL |
tbl_cart_items:
| id | cart_id | name | qty | price | total | added |
|---|---|---|---|---|---|---|
| 1 | 1 | Lemon | 1 | 0.87 | 0.87 | 2022-02-08 12:20:20 |
| 2 | 1 | Apple | 2 | 0.50 | 1.00 | 2022-02-08 12:25:20 |
| 3 | 1 | Banana | 1 | 0.65 | 0.65 | 2022-02-08 12:30:21 |