sql – Database structure for customer table having many orders per customer and many items per order

sql – Database structure for customer table having many orders per customer and many items per order

You need four tables, something like this:

Possible

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.

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.

Heres 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` ;

Theres no sense in creating a table per order. Dont do that. Its not practical, not maintainable. You wont 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

sql – Database structure for customer table having many orders per customer and many items per order

Id 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

Leave a Reply

Your email address will not be published.