“客户”的数据库结构表每个客户有很多订单，每个订单有很多项目 [英] Database structure for "customer" table having many orders per customer and many items per order

问题描述

我试图创建一个数据库，其中每个客户有多个订单（每天新订单），每个订单都有几个项目。我已经计划创建一个客户表，并为每个订单创建一个表，并用items表填充此表。我认为这种方法太复杂和繁琐，因为订单数量可以达到数千，我不认为有数千个表是可维护的。你认为这是一个适当的结构吗？非常感谢任何帮助。

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.

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

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

推荐答案

你需要四个表，像这样：

You need four tables, something like this:

包含客户清单。每个客户一行。将包含所有客户信息 - 其联系方式等。

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

包含命令。每个订单一行。每个订单由客户下达并具有 Customer_ID - 可用于链接回客户记录。

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.

包含订单商品的列表。订单上的每个项目都有一行 - 因此每个订单都可以在此表中生成多个行。订单中的每个商品都是您库存中的商品，因此每行都有一个product_id，其链接到商品表。

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.

包含产品列表。每个产品有一行。类似于customers表，但是对于products - 包含所有的产品详细信息。

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

以下是可用于创建此结构的SQL代码 - 本身叫 mydb ：

Here's the SQL code that you could use to create this structure - it will create a database for itself called 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` ;

这篇关于“客户”的数据库结构表每个客户有很多订单，每个订单有很多项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！