· 10 years ago · Sep 10, 2016, 02:56 AM
1-- MySQL Script generated by MySQL Workbench
2-- 09/09/16 22:54:47
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 AUTO_INCREMENT,
46 `name` VARCHAR(45) NOT NULL,
47 `location` VARCHAR(45) NOT NULL,
48 PRIMARY KEY (`id`),
49 INDEX (`name` ASC))
50ENGINE = InnoDB
51COMMENT = 'Table containing all information about a restaurant.';
52
53
54-- -----------------------------------------------------
55-- Table `RestaurantHelper`.`Menu`
56-- -----------------------------------------------------
57DROP TABLE IF EXISTS `RestaurantHelper`.`Menu` ;
58
59CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Menu` (
60 `id` INT NOT NULL AUTO_INCREMENT,
61 `menuName` VARCHAR(45) NOT NULL,
62 PRIMARY KEY (`id`))
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `RestaurantHelper`.`MenuItem`
68-- -----------------------------------------------------
69DROP TABLE IF EXISTS `RestaurantHelper`.`MenuItem` ;
70
71CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`MenuItem` (
72 `id` INT NOT NULL,
73 `Name` VARCHAR(45) NULL,
74 `Price` VARCHAR(45) NULL,
75 `Description` VARCHAR(45) NULL,
76 PRIMARY KEY (`id`))
77ENGINE = InnoDB
78COMMENT = 'Table containing all items that appear on ALL menus';
79
80
81-- -----------------------------------------------------
82-- Table `RestaurantHelper`.`RestaurantMenus`
83-- -----------------------------------------------------
84DROP TABLE IF EXISTS `RestaurantHelper`.`RestaurantMenus` ;
85
86CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`RestaurantMenus` (
87 `idRestaurant` INT NOT NULL AUTO_INCREMENT,
88 `idMenu` INT NOT NULL,
89 INDEX `idMenu_idx` (`idMenu` ASC),
90 INDEX `idRestaurant_idx` (`idRestaurant` ASC),
91 CONSTRAINT `restaurantmenus_idmenu_fk`
92 FOREIGN KEY (`idMenu`)
93 REFERENCES `RestaurantHelper`.`Menu` (`id`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION,
96 CONSTRAINT `restaurantmenus_idrestaurant_fk`
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`.`Category`
107-- -----------------------------------------------------
108DROP TABLE IF EXISTS `RestaurantHelper`.`Category` ;
109
110CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`Category` (
111 `id` INT NOT NULL AUTO_INCREMENT,
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 `idCategory` INT NOT NULL,
126 INDEX `idMenuItem_idx` (`idMenuItem` ASC),
127 INDEX `idCategory_idx` (`idCategory` ASC),
128 CONSTRAINT `menuitemcategory_idmenuitem_fk`
129 FOREIGN KEY (`idMenuItem`)
130 REFERENCES `RestaurantHelper`.`MenuItem` (`id`)
131 ON DELETE CASCADE
132 ON UPDATE CASCADE,
133 CONSTRAINT `menuitemcategory_idcategory_fk`
134 FOREIGN KEY (`idCategory`)
135 REFERENCES `RestaurantHelper`.`Category` (`id`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION)
138ENGINE = InnoDB
139COMMENT = 'Table relating menuItems and categories. \nLinks items with their respective categories';
140
141
142-- -----------------------------------------------------
143-- Table `RestaurantHelper`.`MenuCategory`
144-- -----------------------------------------------------
145DROP TABLE IF EXISTS `RestaurantHelper`.`MenuCategory` ;
146
147CREATE TABLE IF NOT EXISTS `RestaurantHelper`.`MenuCategory` (
148 `idMenu` INT NOT NULL,
149 `idCategory` INT NOT NULL,
150 INDEX `idMenu_idx` (`idMenu` ASC),
151 INDEX `idMenuCategory_idx` (`idCategory` ASC),
152 CONSTRAINT `menucategory_idmenu_fk`
153 FOREIGN KEY (`idMenu`)
154 REFERENCES `RestaurantHelper`.`Menu` (`id`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `menucategory_idcategory_fk`
158 FOREIGN KEY (`idCategory`)
159 REFERENCES `RestaurantHelper`.`Category` (`id`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164
165SET SQL_MODE=@OLD_SQL_MODE;
166SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
167SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;