hzfqV3ph

1-- MySQL Script generated by MySQL Workbench
2-- 09/09/16 14:32:04
3-- Model: New Model    Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema RestaurantHelper
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `RestaurantHelper` ;
14
15-- -----------------------------------------------------
16-- Schema RestaurantHelper
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `RestaurantHelper` DEFAULT CHARACTER SET utf8 ;
19USE `RestaurantHelper` ;
20
21-- -----------------------------------------------------
22-- Table `RestaurantHelper`.`Users`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `RestaurantHelper`.`Users` ;
25
26CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Users` (
27  `idUsers` INT NOT NULL,
28  `firstName` VARCHAR(45) NULL,
29  `lastName` VARCHAR(45) NULL,
30  `email` VARCHAR(45) NULL,
31  `username` VARCHAR(45) NULL,
32  `password` VARCHAR(45) NULL COMMENT 'hashed obviously\n',
33  `creditCardNumber` VARCHAR(45) NULL COMMENT 'Base64 Encode this and later remove it entirely and replace with paying with paypal',
34  PRIMARY KEY (`idUsers`))
35ENGINE = InnoDB
36COMMENT = 'Table containing information about individual users';
37
38
39-- -----------------------------------------------------
40-- Table `RestaurantHelper`.`Restaurant`
41-- -----------------------------------------------------
42DROP TABLE IF EXISTS `RestaurantHelper`.`Restaurant` ;
43
44CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Restaurant` (
45  `id` INT NOT NULL,
46  `name` VARCHAR(45) NULL,
47  `location` VARCHAR(45) NULL,
48  PRIMARY KEY (`id`))
49ENGINE = InnoDB
50COMMENT = 'Table containing all information about a restaurant.';
51
52
53-- -----------------------------------------------------
54-- Table `RestaurantHelper`.`Menu`
55-- -----------------------------------------------------
56DROP TABLE IF EXISTS `RestaurantHelper`.`Menu` ;
57
58CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Menu` (
59  `id` INT NOT NULL,
60  `id menuItem` INT NOT NULL,
61  PRIMARY KEY (`id`))
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `RestaurantHelper`.`MenuItem`
67-- -----------------------------------------------------
68DROP TABLE IF EXISTS `RestaurantHelper`.`MenuItem` ;
69
70CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`MenuItem` (
71  `id` INT NOT NULL,
72  `Name` VARCHAR(45) NULL,
73  `Price` VARCHAR(45) NULL,
74  `Description` VARCHAR(45) NULL,
75  PRIMARY KEY (`id`))
76ENGINE = InnoDB
77COMMENT = 'Table containing all items that appear on ALL menus';
78
79
80-- -----------------------------------------------------
81-- Table `RestaurantHelper`.`RestaurantMenus`
82-- -----------------------------------------------------
83DROP TABLE IF EXISTS `RestaurantHelper`.`RestaurantMenus` ;
84
85CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`RestaurantMenus` (
86  `idRestaurant` INT NOT NULL,
87  `idMenu` INT NOT NULL,
88  `MenuName` VARCHAR(45) NULL,
89  INDEX `idMenu_idx` (`idMenu` ASC),
90  INDEX `idRestaurant_idx` (`idRestaurant` ASC),
91  CONSTRAINT `idMenu`
92    FOREIGN KEY (`idMenu`)
93    REFERENCES `RestaurantHelper`.`Menu` (`id`)
94    ON DELETE NO ACTION
95    ON UPDATE NO ACTION,
96  CONSTRAINT `idRestaurant`
97    FOREIGN KEY (`idRestaurant`)
98    REFERENCES `RestaurantHelper`.`Restaurant` (`id`)
99    ON DELETE NO ACTION
100    ON UPDATE NO ACTION)
101ENGINE = InnoDB
102COMMENT = 'table linking Restaurants to menus so we can account for restaurants with multiple menus';
103
104
105-- -----------------------------------------------------
106-- Table `RestaurantHelper`.`MenuCategory`
107-- -----------------------------------------------------
108DROP TABLE IF EXISTS `RestaurantHelper`.`MenuCategory` ;
109
110CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`MenuCategory` (
111  `id` INT NOT NULL,
112  `categoryName` VARCHAR(45) NOT NULL,
113  PRIMARY KEY (`id`))
114ENGINE = InnoDB
115COMMENT = 'Contains a table of each category available to be on menus';
116
117
118-- -----------------------------------------------------
119-- Table `RestaurantHelper`.`MenuItemCategory`
120-- -----------------------------------------------------
121DROP TABLE IF EXISTS `RestaurantHelper`.`MenuItemCategory` ;
122
123CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`MenuItemCategory` (
124  `idMenuItem` INT NOT NULL,
125  `idMenuCategory` INT NULL,
126  INDEX `idMenuItem_idx` (`idMenuItem` ASC),
127  INDEX `idMenuCategory_idx` (`idMenuCategory` ASC),
128  CONSTRAINT `idMenuItem`
129    FOREIGN KEY (`idMenuItem`)
130    REFERENCES `RestaurantHelper`.`MenuItem` (`id`)
131    ON DELETE CASCADE
132    ON UPDATE CASCADE,
133  CONSTRAINT `idMenuCategory`
134    FOREIGN KEY (`idMenuCategory`)
135    REFERENCES `RestaurantHelper`.`MenuCategory` (`id`)
136    ON DELETE SET NULL
137    ON UPDATE CASCADE)
138ENGINE = InnoDB
139COMMENT = 'Table relating menuItems and categories.  \nLinks items with their respective categories';
140
141
142SET SQL_MODE=@OLD_SQL_MODE;
143SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
144SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;