MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE customer  -- storing the customer_id on every line item is redundant - check out 3rd normal form
(
  customer_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR (100) NOT NULL,
  customer_address_1 VARCHAR (100) NOT NULL -- can have address_1..n
  --
  -- other fields of particular interest to you
  --
);

CREATE TABLE item
(
  item_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  item_code VARCHAR (25) NOT NULL UNIQUE,
  item_desc VARCHAR (200) NOT NULL,
  item_price DECIMAL(10, 2),
  item_supplier INTEGER NOT NULL -- refers to supplier table - not shown!
  -- 
  -- other fields of interest to you
  --
);

CREATE TABLE receipt  -- often called "orders" but receipt is OK
(
  receipt_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INTEGER NOT NULL,  -- refer to customer table - see below
  
  receipt_total DECIMAL(10, 2),  -- kept updated by a trigger (not shown)
                                 -- can be calculated on the fly or
                                 -- possibly a generated field
  receipt_dt TIMESTAMP NOT NULL,  -- date and time of sale
  receipt_asst INTEGER,  -- refers to the sales assistant table - not shown
  CONSTRAINT rec_cust_fk FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
);

CREATE TABLE line_item
(
  receipt_id INTEGER NOT NULL,
  item_id INTEGER NOT NULL,
  item_qty INTEGER NOT NULL,
  CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id),
  CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id),
  CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id)
);
INSERT INTO customer (customer_name, customer_address_1) VALUES 
('Bill Gates', 'Redmond'), ('Larry Ellison', 'Redwood Shores');
INSERT INTO item (item_code, item_desc, item_price, item_supplier) VALUES
('code_1', 'Michael Jackson CD', 1.5, 56), ('code_2', 'U2 CD', 2, 78), ('code_3', 'UB40 CD', 4, 67),
('code_4', 'U2 DVD', 5, 78), ('code_5', 'Kingston USB 32GB', 25, 23);
INSERT INTO receipt (customer_id, receipt_total, receipt_dt, receipt_asst)
VALUES
(1, 6.5, '2020-06-03 15:23:45.123', 34),
(2, 31 , '2020-06-05 10:54:23.123', 17);




INSERT INTO line_item VALUES
(1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);
SELECT 
  c.customer_id, c.customer_name, c.customer_address_1,
  i.item_desc, i.item_price, 
  r.receipt_id, 
  li.item_id, li.item_qty
FROM 
  customer c
JOIN receipt r 
  ON c.customer_id = r.customer_id
JOIN line_item li 
  ON r.receipt_id = li.receipt_id
JOIN item i 
  ON li.item_id = i.item_id;


customer_id
customer_name
customer_address_1
item_desc
item_price
receipt_id
item_id
item_qty


1
Bill Gates
Redmond
Michael Jackson CD
1.50
1
1
1

1
Bill Gates
Redmond
U2 DVD
5.00
1
4
1

2
Larry Ellison
Redwood Shores
U2 CD
2.00
2
2
1

2
Larry Ellison
Redwood Shores
UB40 CD
4.00
2
3
1

2
Larry Ellison
Redwood Shores
Kingston USB 32GB
25.00
2
5
1

customer_id	customer_name	customer_address_1	item_desc	item_price	receipt_id	item_id	item_qty
1	Bill Gates	Redmond	Michael Jackson CD	1.50	1	1	1
1	Bill Gates	Redmond	U2 DVD	5.00	1	4	1
2	Larry Ellison	Redwood Shores	U2 CD	2.00	2	2	1
2	Larry Ellison	Redwood Shores	UB40 CD	4.00	2	3	1
2	Larry Ellison	Redwood Shores	Kingston USB 32GB	25.00	2	5	1