Add multiple products in one order with the same client id

Admin 1
Viewed 420

There are stack overflow posts about this but I haven't find one with a good explanation so this is what I want to do:

I have an orders, products and a clients table, I need to be able to add multiple products in the orders table. What I found is that I can create another table like order_products to solve this problem but I just don't understand how multiple products are going to be saved and why do this should solve my problem?

I need a full explanation about this, thanks.

mysql sql database
1 Answers

Multiple products can be saved in an order_products table if you will put a reference in each row to the order they belong. Consider this sql (it is very basic to understand the concept):

--I use 'ordination' instead of 'order' because 'order' is
--reserverd word in SQL

CREATE TABLE client(
    id INT(3),
    name VARCHAR(20),
    PRIMARY KEY(id)
);



CREATE TABLE product(
    id INT(3),
    name VARCHAR(20),
    price NUMERIC(4,2), 
    PRIMARY KEY(id)
);


CREATE TABLE ordination(
    id INT(3),
    client_id INT(3),
    ordination_date TIMESTAMP,
    PRIMARY KEY(id),
    FOREIGN KEY(client_id)  REFERENCES client(id)
);

CREATE TABLE ordination_product(
product_id INT(3),
ordination_id INT(3),
quantity INT(3),
PRIMARY KEY(product_id,ordination_id),
FOREIGN KEY(product_id)  REFERENCES product(id),
FOREIGN KEY(ordination_id)  REFERENCES ordination(id)
);

When you create an ordination you follow these steps:

  • Create a row in ordination referencing the client using the client id.
  • Create a row in ordination_product for every product the client ordered referencing the ordination using the ordination id.

If you need to retrive the the products in a specific ordination you can SELECT in the ordination_product table using the ordination_id.

user_123506780
Related