How to create an order details table for a grocery shopping system in MySQL

How to create the order details table of the food shopping system in MySQL

When developing the food shopping system, the order details table is a very important data table. It records the product details in each order, including product ID, quantity, price and other information. This article will introduce how to create an order details table for the grocery shopping system in MySQL, and attach specific code examples.

1. Create database and data table

First, create a database named buy_vegetables in MySQL. You can use the following command:

CREATE DATABASE buy_vegetables;

Then, select the database:

USE buy_vegetables;

Next, create the order details table order_items. The table contains the following fields:

• id: order detail ID, which is an auto-incrementing primary key
• order_id: order ID, and order The order ID in the table is associated with
• product_id: product ID, which is associated with the product ID in the product table
• quantity: product quantity
• price: Product unit price

You can use the following code to create the order_items table:

CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  price DECIMAL(10, 2)
);

2. Add foreign key constraints

To ensure the integrity of the association, foreign key constraints can be added to the order_id and product_id fields. First, create the orders table and the products table, and then add foreign key constraints respectively.

• Create the orders table, containing the following fields:

  • id: order ID, which is an auto-incrementing primary key
  • customer_id: Customer ID, associated with the customer ID in the customer table
  • order_date: Order date

  You can use the following code to create the orders table:

  CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE
  );

  Copy after login

• Create the products table with the following fields:

  • id: Product ID, an auto-incremented primary key
  • name: Product name
  • price: Product price

  You can use the following code to create the products table:

  CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2)
  );

  Copy after login

• To add foreign key constraints, use the following code:

  ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_orders
  FOREIGN KEY (order_id) REFERENCES orders(id),
  ADD CONSTRAINT fk_order_items_products
  FOREIGN KEY (product_id) REFERENCES products(id);

  Copy after login

3. Inserting data example

In order to demonstrate the usage of the order details table, the following is an example SQL statement example to insert data:

INSERT INTO orders (customer_id, order_date)
VALUES (1, '2021-01-01');

INSERT INTO products (name, price)
VALUES ('苹果', 5.99),
       ('橙子', 4.99);

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (1, 1, 2, 11.98),
       (1, 2, 3, 14.97);

The above code is to# Some sample data is inserted into the ##orders table and the products table, and order details are inserted into the order_items table.

The above is the detailed content of How to create an order details table for a grocery shopping system in MySQL. For more information, please follow other related articles on the PHP Chinese website!