E5asHwi5

1-- MySQL Script generated by MySQL Workbench
2-- 09/09/16 14:02:43
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;
36
37
38-- -----------------------------------------------------
39-- Table `RestaurantHelper`.`Restaurant`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `RestaurantHelper`.`Restaurant` ;
42
43CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Restaurant` (
44  `id` INT NOT NULL,
45  `name` VARCHAR(45) NULL,
46  `location` VARCHAR(45) NULL,
47  PRIMARY KEY (`id`))
48ENGINE = InnoDB
49COMMENT = 'Table containing all information about a restaurant.';
50
51
52-- -----------------------------------------------------
53-- Table `RestaurantHelper`.`menu_item`
54-- -----------------------------------------------------
55DROP TABLE IF EXISTS `RestaurantHelper`.`menu_item` ;
56
57CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`menu_item` (
58  `id` INT NOT NULL,
59  `Name` VARCHAR(45) NULL,
60  `Price` VARCHAR(45) NULL,
61  `Description` VARCHAR(45) NULL,
62  `Category` VARCHAR(45) NULL,
63  PRIMARY KEY (`id`))
64ENGINE = InnoDB
65COMMENT = 'Table containing all items that appear on ALL menus';
66
67
68-- -----------------------------------------------------
69-- Table `RestaurantHelper`.`Menu`
70-- -----------------------------------------------------
71DROP TABLE IF EXISTS `RestaurantHelper`.`Menu` ;
72
73CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Menu` (
74  `id` INT NOT NULL,
75  `id menu_items` INT NOT NULL,
76  PRIMARY KEY (`id`),
77  INDEX `id menu_items_idx` (`id menu_items` ASC),
78  CONSTRAINT `id menu_items`
79    FOREIGN KEY (`id menu_items`)
80    REFERENCES `RestaurantHelper`.`menu_item` (`id`)
81    ON DELETE NO ACTION
82    ON UPDATE NO ACTION)
83ENGINE = InnoDB
84COMMENT = 'Contains details on each menu and which restaurant they\'re associated with.';
85
86
87-- -----------------------------------------------------
88-- Table `RestaurantHelper`.`RestaurantMenus`
89-- -----------------------------------------------------
90DROP TABLE IF EXISTS `RestaurantHelper`.`RestaurantMenus` ;
91
92CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`RestaurantMenus` (
93  `idRestaurant` INT NOT NULL,
94  `idMenu` INT NOT NULL,
95  INDEX `idMenu_idx` (`idMenu` ASC),
96  INDEX `idRestaurant_idx` (`idRestaurant` ASC),
97  CONSTRAINT `idMenu`
98    FOREIGN KEY (`idMenu`)
99    REFERENCES `RestaurantHelper`.`Menu` (`id`)
100    ON DELETE NO ACTION
101    ON UPDATE NO ACTION,
102  CONSTRAINT `idRestaurant`
103    FOREIGN KEY (`idRestaurant`)
104    REFERENCES `RestaurantHelper`.`Restaurant` (`id`)
105    ON DELETE NO ACTION
106    ON UPDATE NO ACTION)
107ENGINE = InnoDB
108COMMENT = 'table relating Restaurants to menus so we can account for restaurants with multiple menus';
109
110
111-- -----------------------------------------------------
112-- Table `RestaurantHelper`.`menu_category`
113-- -----------------------------------------------------
114DROP TABLE IF EXISTS `RestaurantHelper`.`menu_category` ;
115
116CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`menu_category` (
117  `id` INT NOT NULL,
118  `category_name` VARCHAR(45) NOT NULL,
119  PRIMARY KEY (`id`))
120ENGINE = InnoDB;
121
122
123-- -----------------------------------------------------
124-- Table `RestaurantHelper`.`menu_item_category`
125-- -----------------------------------------------------
126DROP TABLE IF EXISTS `RestaurantHelper`.`menu_item_category` ;
127
128CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`menu_item_category` (
129  `idMenuItem` INT NOT NULL,
130  `idMenuCategory` INT NULL,
131  INDEX `idMenuItem_idx` (`idMenuItem` ASC),
132  INDEX `idMenuCategory_idx` (`idMenuCategory` ASC),
133  CONSTRAINT `idMenuItem`
134    FOREIGN KEY (`idMenuItem`)
135    REFERENCES `RestaurantHelper`.`menu_item` (`id`)
136    ON DELETE CASCADE
137    ON UPDATE CASCADE,
138  CONSTRAINT `idMenuCategory`
139    FOREIGN KEY (`idMenuCategory`)
140    REFERENCES `RestaurantHelper`.`menu_category` (`id`)
141    ON DELETE SET NULL
142    ON UPDATE CASCADE)
143ENGINE = InnoDB;
144
145
146SET SQL_MODE=@OLD_SQL_MODE;
147SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
148SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;