MySQL workbench is a modern MySQL database management utility that allows developers to (1) design databases, (2) develop SQL statements and (3) manage MySQL server. This article focuses on the database design part. We will use MySQL to design a professional ERD and take advantage of MySQL workbench’s forward engineering utility to automatically generate a complete working database. We will work with a case study.

Case Study Scenario

We will design a simple database for a customer ordering system. Our database will have the following core tables
  • Customers – these will be making orders online
  • Employees – these will be responsible for managing customers and their orders
  • Products – the products that customers will be ordering
  • Orders – this table will hold the orders data
  • Order Details
  • Users – employees will have to login to the admin panel
For now we will work with the above tables. The database that we design will be used to create a customer ordering system(s) in PHP (a web based app) and RESTful API in PHP, C# and Java desktop based apps. REST Clients will be included in C# and Java apps to get the orders posted online and make them available in the desktop apps. Web based apps and RESTful APIs for C# and Java will be considered too if our readers request for them.

Installing MySQL Workbench and complete guide on creating ERD diagrams

If you do not have MySQL workbench already installed, you can download it here and install.  If you would like to be serenaded with the virtues of ERD designing with MySQL workbench then I recommend you check out this article on Guru99.

Design the ERD for our customer ordering system

We make the following assumptions when designing our database. Since our application will be based on the client-server architecture and have an audit trail, all the database tables will have the following common fields
  • date_created
  • date_updated
  • created_from_ip
  • updated_from_ip

Customer Ordering System Database Data Dictionary

Version Control
VersionDescriptionAuthorDate
1Customer Ordering System database v1.0Rodrick Kazembe03-03-2014
Customers TablePersonal details of registered customers
Data MemberDescriptionTypeDefault ValueRequired?Unique?
customer_idCustomer unique identification numberINTNoneYesYes
nameCustomer’s business/personal nameVARCHAR(145)NoneYesNo
emailCustomer’s contact email addressVARCHAR(75)NoneYesYes
contact_personName of the person authorized to communicate on behalf of the customerVARCHAR(75)NoneYesNo
postal_addressPhysical mailing address of the customerVARCHAR(500)NoneNoNo
physical_addressPhysical location of customerVARCHAR(500)NoneYesMo
contact_numberCustomer’s mobile or telephone numberVARCHAR(45)NoneYesNo
employee_idEmployee (representing customer)’s identity numberINTNoneYesNo
date_createdDate when the record is createdDATETIMECURRENT_DATE()YesNo
date_updatedDate when the record is updatedDATETIMECURRENT_DATE()YesNo
created_from_ipClient IP address of the computer used to create the recordVARCHAR(45)NoneYesNo
updated_from_ipClient IP address of the computer used to update the recordVARCHAR(45)NoneyesNo
Employees TablePersonal details of registered employees
Data MemberDescriptionTypeDefault ValueRequired?Unique?
employee_idEmployee unique identification numberINTNoneYesYes
employee_nameEmployee’s full namesVARCHAR(145)NoneYesNo
emailEmployee’s contact email addressVARCHAR(75)NoneYesYes
contact_numberEmployee’s mobile or telephone numberVARCHAR(45)NoneYesNo
positionEmployee’s work job title i.e. sales manager, sales representativeVARCHAR(145)NoneYesNo
date_createdDate when the record is createdDATETIMECURRENT_DATE()YesNo
date_updatedDate when the record is updatedDATETIMECURRENT_DATE()YesNo
created_from_ipClient IP address of the computer used to create the recordVARCHAR(45)NoneYesNo
updated_from_ipClient IP address of the computer used to update the recordVARCHAR(45)NoneyesNo
Products TablePersonal details of registered customers
Data MemberDescriptionTypeDefault ValueRequired?Unique?
product_idProduct unique identification numberINTNoneYesYes
product_nameProduct nameVARCHAR(145)NoneYesNo
descriptionProduct brief descriptionVARCHAR(250)NoneYesYes
quantity_at_handProduct quantity available in inventoryINTNoneYesNo
min_reorder_levelProduct’s minimum reorder levelINTNoneYesNo
max_reorder_levelProduct’s maximum reorder levelINTNoneYesMo
date_createdDate when the record is createdDATETIMECURRENT_DATE()YesNo
date_updatedDate when the record is updatedDATETIMECURRENT_DATE()YesNo
created_from_ipClient IP address of the computer used to create the recordVARCHAR(45)NoneYesNo
updated_from_ipClient IP address of the computer used to update the recordVARCHAR(45)NoneyesNo
Orders TablePersonal details of registered customers
Data MemberDescriptionTypeDefault ValueRequired?Unique?
order_idOrder unique identification numberINTNoneYesYes
customer_idCustomer unique identification numberINTNoneYesNo
trans_dateDate transaction madeDATENoneYesYes
delivery_dateDate that the product should be deliveredDATENoneNoNo
canceledBoolean field that indicates whether the product order has been cancelled or not. True or 1 indicates the order has been canceled.BOOLEAN0YesNo
deliveredBoolean field that indicates whether the ordered products have been delivered or not. True or 1 indicates the products have been delivered.BOOLEAN0YesNo
remarksAny special notes made during the order.VARCHAR(250)NoneNoNo
date_createdDate when the record is createdDATETIMECURRENT_DATE()YesNo
date_updatedDate when the record is updatedDATETIMECURRENT_DATE()YesNo
created_from_ipClient IP address of the computer used to create the recordVARCHAR(45)NoneYesNo
updated_from_ipClient IP address of the computer used to update the recordVARCHAR(45)NoneyesNo
Order Details TableDetailed order information
Data MemberDescriptionTypeDefault ValueRequired?Unique?
record_idRecord unique identification numberINTNoneYesYes
order_idOrder unique identification numberINTNoneYesNo
product_idProduct unique identification numberINTNoneYesMo
order_quantityRequested quantity by the customerINTNoneYesNo
date_createdDate when the record is createdDATETIMECURRENT_DATE()YesNo
date_updatedDate when the record is updatedDATETIMECURRENT_DATE()YesNo
created_from_ipClient IP address of the computer used to create the recordVARCHAR(45)NoneYesNo
updated_from_ipClient IP address of the computer used to update the recordVARCHAR(45)NoneyesNo
Users TablePersonal details of registered system users
Data MemberDescriptionTypeDefault ValueRequired?Unique?
record_idUser unique identification numberINTNoneYesYes
user_idVARCHAR(5)NoneYesYes
user_nameUser’s full namesVARCHAR(145)NoneYesYes
emailUser’s contact email addressVARCHAR(75)NoneYesYes
blockedBoolean field indicating whether a user has been blocked from accessing the system or not. True or 1 indicates that the user has been blockedBOOLEAN1YesNo
passwordUser’s password hashVARCHAR(145)NoneYesNo
date_createdDate when the record is createdDATETIMECURRENT_DATE()YesNo
date_updatedDate when the record is updatedDATETIMECURRENT_DATE()YesNo
created_from_ipClient IP address of the computer used to create the recordVARCHAR(45)NoneYesNo
updated_from_ipClient IP address of the computer used to update the recordVARCHAR(45)NoneyesNo
Use the knowledge you acquired from the article on creating ERD diagrams and the above data dictionary to create your ERD diagram. The following table shows the relationships between the tables
SR NORELATIONSHIPNATUREDESCRIPTION
1Employees to Customers1 to ManyEmployees can manager more than one customer. A customer can only be managed by a single employee at a time
2Customers to Orders1 to ManyCustomers can have more than one order
3Orders to Order Details1 to ManyOrders can have more than one product ordered
4Products to Order Details1 to ManyA product can be ordered more than once

Completed MySQL Workbench ERD diagram

mysql_workbench_erd

How to generate the database script using MySQL workbench from the ERD diagram

  • Click on database menu
  • Select forward engineering menu
  • Follow the wizard prompts

Customer orders database scripts

CREATE schema odering_system;


-- -----------------------------------------------------
-- Table `odering_system`.`employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `odering_system`.`employees` (
  `employee_id` INT NOT NULL AUTO_INCREMENT,
  `employee_name` VARCHAR(145) NULL,
  `email` VARCHAR(75) NULL,
  `contact_number` VARCHAR(45) NULL,
  `position` VARCHAR(145) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  PRIMARY KEY (`employee_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `odering_system`.`customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `odering_system`.`customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(145) NULL,
  `email` VARCHAR(75) NULL,
  `contact_person` VARCHAR(75) NULL,
  `postal_address` VARCHAR(500) NULL,
  `physical_address` VARCHAR(500) NULL,
  `contact_number` VARCHAR(45) NULL,
  `employee_id` INT NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  PRIMARY KEY (`customer_id`),
  INDEX `fk_customers_employees1_idx` (`employee_id` ASC),
  CONSTRAINT `fk_customers_employees1`
    FOREIGN KEY (`employee_id`)
    REFERENCES `odering_system`.`employees` (`employee_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `odering_system`.`orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `odering_system`.`orders` (
  `order_id` INT NOT NULL,
  `customer_id` INT NULL,
  `trans_date` VARCHAR(45) NULL,
  `delivery_date` VARCHAR(45) NULL,
  `canceled` TINYINT(1) NULL,
  `delivered` TINYINT(1) NULL,
  `remarks` VARCHAR(250) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  PRIMARY KEY (`order_id`),
  INDEX `fk_orders_customers1_idx` (`customer_id` ASC),
  CONSTRAINT `fk_orders_customers1`
    FOREIGN KEY (`customer_id`)
    REFERENCES `odering_system`.`customers` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `odering_system`.`products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `odering_system`.`products` (
  `product_id` INT NOT NULL,
  `product_name` VARCHAR(145) NULL,
  `description` VARCHAR(250) NULL,
  `quantity_at_hand` INT NULL,
  `min_reorder_level` INT NULL,
  `max_reorder_level` INT NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  PRIMARY KEY (`product_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `odering_system`.`order_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `odering_system`.`order_details` (
  `record_id` INT NOT NULL,
  `order_id` INT NULL,
  `product_id` INT NULL,
  `order_quantity` INT NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  PRIMARY KEY (`record_id`),
  INDEX `fk_order_details_products_idx` (`product_id` ASC),
  INDEX `fk_order_details_orders1_idx` (`order_id` ASC),
  CONSTRAINT `fk_order_details_products`
    FOREIGN KEY (`product_id`)
    REFERENCES `odering_system`.`products` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_order_details_orders1`
    FOREIGN KEY (`order_id`)
    REFERENCES `odering_system`.`orders` (`order_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `odering_system`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `odering_system`.`users` (
  `record_id` INT NOT NULL,
  `user_id` VARCHAR(5) NOT NULL,
  `user_name` VARCHAR(145) NULL,
  `email` VARCHAR(45) NULL,
  `blocked` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  PRIMARY KEY (`record_id`))
ENGINE = InnoDB;