MySQL Table Design Guide: How to Create Order Table and Product Table

MySQL Table Design Guide: How to Create Order Tables and Product Tables

Introduction
In database design, it is very important to create tables correctly. This article will focus on how to create the order table and product table to provide a guide for readers to refer to. At the same time, for a better understanding, this article will also provide relevant code examples.

Order table design
The order table is a table used to store order information. The following is a simple order table design example:

CREATE TABLE orders (

order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
shipping_address VARCHAR(255),
CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)

);

In this example, we create an order table named "orders" The table contains the following fields:

• order_id: order number, which grows automatically as the primary key.
• Customer_id: Customer ID, connected to the customer_id field of another table "customers", used to associate customer information.
• order_date: Order date, using DATE type.
• total_amount: The total amount of the order, using the DECIMAL(10, 2) type, representing up to 10 digits, 2 of which are decimals.
• shipping_address: Shipping address, using VARCHAR(255) type, indicating a maximum length of 255 characters.

In this table, we also express the foreign key relationship through "CONSTRAINT" to associate customer_id with the customer_id field of another table "customers".

Product table design
The product table is a table used to store product information. The following is a simple product table design example:

CREATE TABLE products (

product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
price DECIMAL(10, 2),
description TEXT

);

In this example, we create a table named "products" The table contains the following fields:

• product_id: product number, which grows automatically as the primary key.
• product_name: Product name, using VARCHAR(255) type, indicating a maximum length of 255 characters.
• price: Product price, using DECIMAL(10, 2) type, representing up to 10 digits, 2 of which are decimals.
• description: Product description, using TEXT type, which means that larger text content can be stored.

Code Examples
To better understand the design and use of tables, some simple code examples are provided below.

1. Insert order data

INSERT INTO orders (customer_id, order_date, total_amount, shipping_address)
VALUES (1, '2021-01-01', 100.00, '123 Main St');

2. Query order data

SELECT * FROM orders
WHERE customer_id = 1;

3. Insert Product data

INSERT INTO products (product_name, price, description)
VALUES ('iPhone', 999.99, 'The latest iPhone model');

4. Query Product data

SELECT * FROM products
WHERE price > 500.00;

Summary
By correctly creating the order table and product table, we can better manage and save order and product information. Table design needs to take into account field types, lengths, constraints and other factors to meet actual needs. This article provides a simple guide with relevant code examples that we hope will be helpful to readers.

The above is the detailed content of MySQL Table Design Guide: How to Create Order Table and Product Table. For more information, please follow other related articles on the PHP Chinese website!