Mark’s Site

From Diagram to Schema

April 17th, 2008 | by admin | |

When you have your database diagram at a point where you can see all the relationships among your entities, it’s time to get a database schema output of your diagram. The MySQL WorkBench does a great job of creating your schema based on the diagram you create. MSSQL Management Studio is another product that is great at visual development, going from ER diagram, to creating your schema for you. There’s no reason why you can’t use MySQL’s WorkBench (because at time of writing it is free to use), but if you use a different product that does not automatically generate your schema for you, you’ll have to do the schema manually (which i highly recommend as it will teach you more about Sql), however that lies outside the scope of this article

Once you have your schema generated, the first thing you need to do is run it on a database to make sure the generation process was truly successful and no problems occur. If your application is hosted by a hosting company, they will have tools in place to let you login to your SQL server and run scripts like this. If not, find another hosting provider

If problems occur, you’ll need to debug your schema (cut parts out, see what is stopping it from running successfully) until you figure out the issues and fix them.

When your schema has run successfully, you are ready to start coding

Real world example

The database diagram you saw earlier of my website was very easy to convert to a SQL schema. Using MySQL’s WordBench software that helped to create the database diagram, I just exported to SQL script Makes live so much easier than writing it yourself, trust me, I’ve been there While writing SQL yourself is very important for your understanding of SQL, once you know your way around all the main syntax, why not let software do the job for you? And the beauty of this WorkBench software is you can SEE all of your tables as entities in a database diagram, and all of the relationships between tables, which, when writing SQL from scratch, can be difficult to get your head around at times, especially for large systems.

This was the output (pretty cool huh?)

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

-- -----------------------------------------------------
-- Table `person`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `person` ;

CREATE  TABLE IF NOT EXISTS `person` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(255) NULL ,
  `last_name` VARCHAR(255) NULL ,
  `email_address` VARCHAR(255) NULL ,
  `phone_number` VARCHAR(255) NULL ,
  `mobile_number` VARCHAR(255) NULL ,
  `address` TEXT NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NOT NULL ,
  PRIMARY KEY (`id`) )
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `role` ;

CREATE  TABLE IF NOT EXISTS `role` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `description` VARCHAR(255) NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `priority` INT NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_role_member (`created_by_member_id` ASC) ,
  INDEX fk_role_member1 (`modified_by_member_id` ASC) ,
  CONSTRAINT `fk_role_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_role_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member` ;

CREATE  TABLE IF NOT EXISTS `member` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `person_id` INT UNSIGNED NULL ,
  `role_id` INT UNSIGNED NULL ,
  `login_name` VARCHAR(45) NULL ,
  `login_password` VARCHAR(45) NULL ,
  `bad_login_attempts` INT UNSIGNED NULL ,
  `bad_login_inactive_time_set` DATETIME NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_member_person (`person_id` ASC) ,
  INDEX fk_member_member (`created_by_member_id` ASC) ,
  INDEX fk_member_member1 (`modified_by_member_id` ASC) ,
  INDEX fk_member_role (`role_id` ASC) ,
  CONSTRAINT `fk_member_person`
    FOREIGN KEY (`person_id` )
    REFERENCES `person` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `menu_item`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `menu_item` ;

CREATE  TABLE IF NOT EXISTS `menu_item` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `seo_name` VARCHAR(255) NULL ,
  `display_order` INT UNSIGNED NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_menu_item_member (`created_by_member_id` ASC) ,
  INDEX fk_menu_item_member1 (`modified_by_member_id` ASC) ,
  CONSTRAINT `fk_menu_item_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_menu_item_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `member_session`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `member_session` ;

CREATE  TABLE IF NOT EXISTS `member_session` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `login_time` DATETIME NULL ,
  `login_ip_address` VARCHAR(255) NULL ,
  `member_id` INT UNSIGNED NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_session_member (`member_id` ASC) ,
  CONSTRAINT `fk_session_member`
    FOREIGN KEY (`member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `policy_area`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `policy_area` ;

CREATE  TABLE IF NOT EXISTS `policy_area` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `permissions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `permissions` ;

CREATE  TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `view_all_permission` VARCHAR(1) NULL ,
  `view_own_permission` VARCHAR(1) NULL ,
  `modify_permission` VARCHAR(1) NULL ,
  `delete_permission` VARCHAR(1) NULL ,
  `role_id` INT UNSIGNED NULL ,
  `policy_area_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_permissions_role (`role_id` ASC) ,
  INDEX fk_permissions_module (`policy_area_id` ASC) ,
  CONSTRAINT `fk_permissions_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_permissions_module`
    FOREIGN KEY (`policy_area_id` )
    REFERENCES `policy_area` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `page_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_category` ;

CREATE  TABLE IF NOT EXISTS `page_category` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `seo_name` VARCHAR(255) NULL ,
  `parent_page_category_id` INT UNSIGNED NULL ,
  `display_order` INT UNSIGNED NULL ,
  `menu_item_id` INT UNSIGNED NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_page_category_menu_item (`menu_item_id` ASC) ,
  INDEX fk_page_category_member (`created_by_member_id` ASC) ,
  INDEX fk_page_category_member1 (`modified_by_member_id` ASC) ,
  INDEX fk_page_category_page_category (`parent_page_category_id` ASC) ,
  CONSTRAINT `fk_page_category_menu_item`
    FOREIGN KEY (`menu_item_id` )
    REFERENCES `menu_item` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_category_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_category_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_category_page_category`
    FOREIGN KEY (`parent_page_category_id` )
    REFERENCES `page_category` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `page`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page` ;

CREATE  TABLE IF NOT EXISTS `page` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `page_category_id` INT UNSIGNED NULL ,
  `role_id` INT UNSIGNED NULL ,
  `name` VARCHAR(255) NULL ,
  `seo_name` VARCHAR(255) NULL ,
  `summary` TEXT NULL ,
  `content` MEDIUMTEXT NULL ,
  `meta_keywords` VARCHAR(255) NULL ,
  `meta_description` VARCHAR(255) NULL ,
  `view_count` INT UNSIGNED NULL ,
  `display_order` INT UNSIGNED NULL ,
  `option_allow_comments` VARCHAR(1) NULL ,
  `option_show_on_front_page` VARCHAR(1) NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_page_page_category (`page_category_id` ASC) ,
  INDEX fk_page_member (`created_by_member_id` ASC) ,
  INDEX fk_page_member1 (`modified_by_member_id` ASC) ,
  INDEX fk_page_role (`role_id` ASC) ,
  CONSTRAINT `fk_page_page_category`
    FOREIGN KEY (`page_category_id` )
    REFERENCES `page_category` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_role`
    FOREIGN KEY (`role_id` )
    REFERENCES `role` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
PACK_KEYS = 0
ROW_FORMAT = DEFAULT;

-- -----------------------------------------------------
-- Table `page_comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_comment` ;

CREATE  TABLE IF NOT EXISTS `page_comment` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `article_id` INT UNSIGNED NULL ,
  `comment` TEXT NULL ,
  `comment_made_by` VARCHAR(45) NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_article_comment_article (`article_id` ASC) ,
  CONSTRAINT `fk_article_comment_article`
    FOREIGN KEY (`article_id` )
    REFERENCES `page` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `page_rating`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_rating` ;

CREATE  TABLE IF NOT EXISTS `page_rating` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `rating` INT NULL ,
  `article_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_article_rating_article (`article_id` ASC) ,
  CONSTRAINT `fk_article_rating_article`
    FOREIGN KEY (`article_id` )
    REFERENCES `page` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `document`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `document` ;

CREATE  TABLE IF NOT EXISTS `document` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `filename` VARCHAR(45) NULL ,
  `filepath` VARCHAR(45) NULL ,
  `filesize` VARCHAR(45) NULL ,
  `filetype` VARCHAR(45) NULL ,
  `created_by_member_id` INT UNSIGNED NULL ,
  `created_date` DATETIME NULL ,
  `modified_by_member_id` INT UNSIGNED NULL ,
  `modified_date` DATETIME NULL ,
  `recordstatus` VARCHAR(1) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_document_member (`created_by_member_id` ASC) ,
  INDEX fk_document_member1 (`modified_by_member_id` ASC) ,
  CONSTRAINT `fk_document_member`
    FOREIGN KEY (`created_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_document_member1`
    FOREIGN KEY (`modified_by_member_id` )
    REFERENCES `member` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `page_document_rel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `page_document_rel` ;

CREATE  TABLE IF NOT EXISTS `page_document_rel` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `page_id` INT UNSIGNED NULL ,
  `document_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX fk_page_document_rel_page (`page_id` ASC) ,
  INDEX fk_page_document_rel_document (`document_id` ASC) ,
  CONSTRAINT `fk_page_document_rel_page`
    FOREIGN KEY (`page_id` )
    REFERENCES `page` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_page_document_rel_document`
    FOREIGN KEY (`document_id` )
    REFERENCES `document` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

From Diagram to Schema

Real world example

Post a Comment

Categories

Search Posts

New Post Notification

Recent Comments

Recent Post