The idea was to create a simplified database that could be used to maintain an online artist's portfolio. The main entity would be a portfolio item that could be categorized and tagged with keywords. The categories are hierarchical while tags are not. Each portfolio item would also have an associated image collection(album).Each image could be assigned a numeric sort order to control eventual display in a gallery on a website.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `portfolio` DEFAULT CHARACTER SET utf8 ;
USE `portfolio` ;

-- -----------------------------------------------------
-- Table `portfolio`.`images`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`images` (
  `image_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(45) NULL DEFAULT NULL ,
  `alt` TINYTEXT NULL DEFAULT NULL ,
  `filename` VARCHAR(60) NOT NULL ,
  `filepath` VARCHAR(45) NULL DEFAULT NULL ,
  `height` SMALLINT(5) UNSIGNED NOT NULL ,
  `width` SMALLINT(5) UNSIGNED NOT NULL ,
  `date_uploaded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`image_id`) ,
  UNIQUE INDEX `file_name_UNIQUE` (`filename` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `portfolio`.`categories`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`categories` (
  `category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `description` VARCHAR(500) NULL DEFAULT NULL ,
  `parent_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`category_id`) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
  INDEX `parent_id` (`parent_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `portfolio`.`portfolio_items`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`portfolio_items` (
  `item_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `category_id` INT(10) UNSIGNED NOT NULL ,
  `title` VARCHAR(45) NULL DEFAULT NULL ,
  `date_created` DATE NULL DEFAULT NULL ,
  `description` MEDIUMTEXT NULL DEFAULT NULL ,
  `date_posted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`item_id`) ,
  INDEX `category_id` (`category_id` ASC) ,
  CONSTRAINT `portfolio_items_ibfk_1`
    FOREIGN KEY (`category_id` )
    REFERENCES `portfolio`.`categories` (`category_id` ))
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8
COMMENT = '		';


-- -----------------------------------------------------
-- Table `portfolio`.`albums`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`albums` (
  `album_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `item_id` INT(10) UNSIGNED NOT NULL ,
  `title` VARCHAR(45) NULL DEFAULT NULL ,
  `description` VARCHAR(250) NULL DEFAULT NULL ,
  `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`album_id`) ,
  INDEX `item_id` (`item_id` ASC) ,
  CONSTRAINT `albums_ibfk_1`
    FOREIGN KEY (`item_id` )
    REFERENCES `portfolio`.`portfolio_items` (`item_id` )
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `portfolio`.`album_images`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`album_images` (
  `album_id` INT(10) UNSIGNED NOT NULL ,
  `image_id` INT(10) UNSIGNED NOT NULL ,
  `sort` TINYINT(3) UNSIGNED NOT NULL ,
  PRIMARY KEY (`album_id`, `image_id`) ,
  INDEX `image_id` (`image_id` ASC) ,
  INDEX `album_id` (`album_id` ASC) ,
  CONSTRAINT `album_images_ibfk_1`
    FOREIGN KEY (`image_id` )
    REFERENCES `portfolio`.`images` (`image_id` )
    ON UPDATE CASCADE,
  CONSTRAINT `album_images_ibfk_2`
    FOREIGN KEY (`album_id` )
    REFERENCES `portfolio`.`albums` (`album_id` )
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `portfolio`.`tags`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`tags` (
  `tag_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `description` VARCHAR(500) NULL DEFAULT NULL ,
  PRIMARY KEY (`tag_id`) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `portfolio`.`item_tags`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `portfolio`.`item_tags` (
  `item_id` INT(10) UNSIGNED NOT NULL ,
  `tag_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`item_id`, `tag_id`) ,
  INDEX `tag_index` (`tag_id` ASC) ,
  INDEX `item_id` (`item_id` ASC) ,
  CONSTRAINT `item_tags_ibfk_1`
    FOREIGN KEY (`item_id` )
    REFERENCES `portfolio`.`portfolio_items` (`item_id` ),
  CONSTRAINT `item_tags_ibfk_2`
    FOREIGN KEY (`tag_id` )
    REFERENCES `portfolio`.`tags` (`tag_id` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `portfolio` ;


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