“客户”表的数据库结构，每个客户有多个订单，每个订单有多个项目

I am trying to create a database where each customer has several orders(new orders daily) and each order has several items. I had planned creating a table of customers and creating a table per order and populating this table with an "items" table. I think this approach is too complicated and cumbersome since the number of orders can reach the thousands, I don't think having thousands of tables is maintainable. What do you think would be an appropriate structure for this? Any help is greatly appreciated.

我正在尝试创建一个数据库，其中每个客户有几个订单(每日新订单)，每个订单有几个项目。我计划创建一个客户表，并按订单创建一个表，并用“items”表填充这个表。我认为这种方法太复杂、太麻烦，因为订单的数量可以达到数千个，我不认为有成千上万个表是可维护的。你认为什么是合适的结构?非常感谢您的帮助。

Sorry if this is a noobish question, I am learning to program. And this is my first ever attempt at database design.

对不起，如果这是一个无聊的问题，我正在学习编程。这是我第一次尝试数据库设计。

3 个解决方案

#1

You need four tables, something like this:

你需要四张桌子，像这样:

Customers

Contains a list of customers. One row per Customer. Would contain all the customers information - their contact details, etc...

包含客户列表。每个客户一行。将包含所有的客户信息-他们的联系方式，等等…

Orders

Contains a list of orders. One row per order. Each order is placed by a customer and has a Customer_ID - which can be used to link back to the customer record. Might also store the delivery address, if different from the customers address from their record - or store addresses in separate tables.

包含订单列表。一行每秩序。每个订单都由客户放置，并具有Customer_ID—可用于链接回客户记录。如果客户地址与他们的记录地址不同，也可以将送货地址存储在单独的表中。

OrderItems

Contains a list of order items. One row for each item on an order - so each Order can generate multiple rows in this table. Each item ordered is a product from your inventory, so each row has a product_id, which links to the products table.

包含订单项的列表。一个订单中的每个项目对应一行——因此每个订单可以在此表中生成多个行。所订购的每个项目都是库存中的产品，因此每一行都有一个product_id，该id链接到products表。

Products

Contains a list of products. One row per product. Similar to the customers table, but for products - contains all the product details.

包含产品列表。每个产品一行。类似于客户表，但对于产品-包含所有产品细节。

Here's the SQL code that you could use to create this structure - it will create a database for itself called mydb:

下面是创建这个结构的SQL代码——它将为自己创建一个名为mydb的数据库:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Customer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Customer` (
  `ID` INT NOT NULL ,
  `Name` TEXT NOT NULL ,
  `PhoneNo` VARCHAR(45) NULL ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Order`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Order` (
  `ID` INT NOT NULL ,
  `customer_id` INT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `fk_Order_1_idx` (`customer_id` ASC) ,
  CONSTRAINT `fk_Order_1`
    FOREIGN KEY (`customer_id` )
    REFERENCES `mydb`.`Customer` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Product`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Product` (
  `ID` INT NOT NULL ,
  `Name` VARCHAR(45) NOT NULL ,
  `Description` TEXT NULL ,
  PRIMARY KEY (`ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`OrderItem`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`OrderItem` (
  `ID` INT NOT NULL ,
  `Order_ID` INT NOT NULL ,
  `Product_ID` INT NOT NULL ,
  `Quantity` INT NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `fk_OrderItem_1_idx` (`Order_ID` ASC) ,
  INDEX `fk_OrderItem_2_idx` (`Product_ID` ASC) ,
  CONSTRAINT `fk_OrderItem_1`
    FOREIGN KEY (`Order_ID` )
    REFERENCES `mydb`.`Order` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_OrderItem_2`
    FOREIGN KEY (`Product_ID` )
    REFERENCES `mydb`.`Product` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;

#2

There's no sense in creating a table per order. Don't do that. It's not practical, not maintainable. You won't be able to normally query your data. For starters all you need just four tables like this

按顺序创建表是没有意义的。不要这样做。它是不实用的，不可维护的。您将无法正常查询数据。首先，你只需要四张这样的桌子。

• customers
• 客户
• orders
• 订单
• order_items
• order_items
• products (or items)
• 产品(或商品)

Here is oversimplified SQLFiddle demo

这里是简化版的sql小提琴演示

#3

I'd have something like a customer table along with orders and items tables. The primary key of customer is the foreign key of order. Items will then have a foreign key that matches the order it was placed on.

我想要一个客户表，以及订单和项目表。客户的主键是订单的外键。然后，项将有一个外键，该外键与它所处的顺序相匹配。

3 tables should be fine

3张桌子应该没问题