SQL: How to have multiple product_id in a single sales_id without separating products using delimiters?

Question

In the requirements given to me, multiple products can be assigned to a single sale. However, I need help how this can be done in SQL and how would the table look like. Below is what my schema looks like:

Sample Schema

In the sales fact, I also included a column containing the aggregate sales for all the product included in the sale.

Ps. Same case with the Products but with Official_Receipt_Id as a single sale can also have many Official_Receipt_Id.

Help will be highly appreciated.

Answer 1

The proper way to do this is to use multiple tables and JOIN them in your queries.

For example:

CREATE TABLE product (
`product_id` VARCHAR(20) NOT NULL PRIMARY KEY,
`product_name` VARCHAR(50)
);

CREATE TABLE sales (
`sales_id` VARCHAR(20) NOT NULL PRIMARY KEY,
`customer_id` VARCHAR(20),
`total_price` INT
);

CREATE TABLE sales_product (
`sales_id` INT NOT NULL,
`product_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`sales_id`, `product_id`)
);

The sales_product table contains one row for each product a sale is associated with and effectively links the product and sales tables together. It would look something like this.

"Sale1" | "Product1"
"Sale1" | "Product2"
"Sale1" | "Product3"
"Sale2" | "Product1"
"Sale2" | "Product2"

When you want to retrieve a sale and all of the products you can do something like this:

SELECT s.*, p.*
FROM sales s
INNER JOIN sales_product sp
ON sp.person_id = s.sales_id
INNER JOIN product p
ON p.product_id = sp.product_id