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

Question

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.

Answer 1

You need four tables, something like this:

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