How to design a maintainable MySQL table structure to implement online ordering function?

How to design a maintainable MySQL table structure to implement the online ordering function?

In today’s mobile Internet era, ordering food online has become an indispensable part of people’s daily lives. In order to realize the normal operation of the online ordering function, a reasonable and maintainable database table structure is needed to store relevant data. This article will introduce how to design a maintainable MySQL table structure to implement the online ordering function, and provide relevant code examples to help readers better understand and practice.

1. User table (User)

The user table is used to store all registered user information, including user ID, user name, password, mobile phone number, etc. Among them, the user ID is used as the primary key to uniquely identify each user.

CREATE TABLE `User` (
  `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Merchant table (Merchant)

The merchant table is used to store all registered merchant information, including merchant ID, merchant name, address, contact information, etc. The merchant ID is used as the primary key to uniquely identify each merchant.

CREATE TABLE `Merchant` (
  `merchant_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `merchant_name` VARCHAR(100) NOT NULL,
  `address` VARCHAR(200) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Dish category table (Category)

The dish category table is used to store all dish classification information, including category ID, category name, etc. The category ID is used as the primary key to uniquely identify each dish category.

CREATE TABLE `Category` (
  `category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Dish table (Dish)

The dish table is used to store all dish information, including dish ID, dish name, dish price, dish classification, etc. The dish ID is used as the primary key to uniquely identify each dish. The dish classification is related to the dish classification table using foreign keys.

CREATE TABLE `Dish` (
  `dish_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dish_name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  `category_id` INT(10) UNSIGNED NULL,
  PRIMARY KEY (`dish_id`),
  CONSTRAINT `FK_Category` FOREIGN KEY (`category_id`) REFERENCES `Category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5. Order table (Order)

The order table is used to store the user's order information, including order ID, user ID, merchant ID, order time, order status wait. The order ID is used as the primary key to uniquely identify each order. The user ID and merchant ID are related to the user table and merchant table using foreign keys.

CREATE TABLE `Order` (
  `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `merchant_id` INT(10) UNSIGNED NOT NULL,
  `order_time` DATETIME NOT NULL,
  `status` ENUM ('待付款', '已付款', '已取消') NOT NULL,
  PRIMARY KEY (`order_id`),
  CONSTRAINT `FK_User` FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`),
  CONSTRAINT `FK_Merchant` FOREIGN KEY (`merchant_id`) REFERENCES `Merchant` (`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Through the design of the above MySQL table structure, we can implement a basic online ordering function. Of course, according to actual needs, we can continue to optimize and expand the table structure and add more fields to store more specific information.

I hope this article will help readers understand how to design a maintainable MySQL table structure to implement the online ordering function. By designing a reasonable table structure, the performance and maintainability of the system can be improved, making the system more stable and efficient in long-term operation and maintenance.

The above is the detailed content of How to design a maintainable MySQL table structure to implement online ordering function?. For more information, please follow other related articles on the PHP Chinese website!