I have to store the Id of all users that buy a product on my website in a MySQL table, in order for each user can be all the products bought by him. I don't want to create a table for each user, is there an easier way to store this kind of data than separating it with commas?
Example of the table:
| Product_name | Price | Id_users_that_bought |
|---|---|---|
| :**********: | $55 | :9;1;510;77;: |
Thanks in advance.
This is a very simplistic view but gives an idea of a typical structure for this.
users
| id | name |
|---|---|
| 1 | User 1 |
| 2 | User 2 |
products
| id | name | description | price |
|---|---|---|---|
| 1 | Product 1 | descr | 55 |
| 2 | Product 2 | descr | 65 |
| 3 | Product 3 | descr | 45 |
| 4 | Product 4 | descr | 75 |
orders
| id | user_id | date | status |
|---|---|---|---|
| 1 | 1 | 2022-01-18 21:03:22 | new |
| 2 | 2 | 2022-01-18 21:07:03 | new |
orders_products
| order_id | product_id | quantity | price |
|---|---|---|---|
| 1 | 1 | 1 | 55 |
| 1 | 2 | 1 | 65 |
| 1 | 3 | 1 | 45 |
| 2 | 2 | 1 | 65 |
| 2 | 4 | 1 | 75 |
Storing the potentially redundant price in orders_products can be handy as prices may change over time.
With a structure along these lines it is trivial to query -
/* find all products bought by "User 1" */
SELECT p.*
FROM users u
JOIN orders o
ON u.id = o.user_id
JOIN orders_products op
ON o.id = op.order_id
JOIN products p
ON op.product_id = p.id
WHERE u.name = 'User 1';
/* find all users who bought "Product 2" */
SELECT u.*
FROM products p
JOIN orders_products op
ON p.id = op.product_id
JOIN orders o
ON op.order_id = o.id
JOIN users u
ON o.user_id = u.id
WHERE p.name = 'Product 2'
And here's a db<>fiddle for you to play with.
