每个客户有许多订单,每个订单有许多物品的 "客户 "表的数据库结构[英] Database structure for "customer" table having many orders per customer and many items per order

本文是小编为大家收集整理的关于每个客户有许多订单,每个订单有许多物品的 "客户 "表的数据库结构的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在尝试创建一个数据库,每个客户都有几个订单(每天新订单),并且每个订单都有几个项目.我计划创建一张客户表,并每订单创建一个表格,并使用"项目"表填充此表.我认为这种方法太复杂且笨重,因为订单的数量可以达到数千个,我认为拥有数千张桌子是可以维护的.您认为这是一个适当的结构?任何帮助都非常感谢.

对不起,如果这是一个努力的问题,我正在学习编程.这是我第一次尝试数据库设计.

推荐答案

您需要四个桌子,类似的东西:

可能的简化数据库模型

客户

包含客户列表.每个客户一排.将包含所有客户的信息 - 他们的联系方式等...

订单

包含订单列表.每个订单一行.每个订单由客户下达,并具有Customer_ID - 可用于链接到客户记录.如果与客户地址不同的记录或在单独的表中商店地址不同,则可能还存储交货地址.

OrderItems

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

产品

包含产品列表.每产品一排.与客户表相似,但对于产品 - 包含所有产品详细信息.

这是您可以用来创建此结构的SQL代码 - 它将为其本身创建一个名为mydb的数据库:

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

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


-- -----------------------------------------------------
-- Table `mydb`.`Orders`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Orders` (
  `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`.`Customers` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `mydb`.`OrderItems`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`OrderItems` (
  `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`.`Orders` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_OrderItem_2`
    FOREIGN KEY (`Product_ID` )
    REFERENCES `mydb`.`Products` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;

其他推荐答案

没有任何订单创建表的感觉.不要那样做.这是不切实际的,不可维护的.您将无法正常查询数据.对于初学者,您只需要这样的四张桌子

  • 客户
  • 订单
  • order_items
  • 产品(或项目)

这是过度简化 其他推荐答案

我将拥有类似客户桌的东西以及订单和项目表.客户的主要关键是订单的外键.然后,项目将具有与下订单相匹配的外键.

3桌子应该很好

本文地址:https://www.itbaoku.cn/post/597676.html

问题描述

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.

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:

Possible Simplified Database Model

Customers

Contains a list of customers. One row per Customer. Would contain all the customer's 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.

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.

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:

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

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


-- -----------------------------------------------------
-- Table `mydb`.`Orders`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Orders` (
  `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`.`Customers` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `mydb`.`OrderItems`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`OrderItems` (
  `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`.`Orders` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_OrderItem_2`
    FOREIGN KEY (`Product_ID` )
    REFERENCES `mydb`.`Products` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `mydb` ;

其他推荐答案

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
  • products (or items)

Here is oversimplified SQLFiddle demo

其他推荐答案

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