· 7 years ago · Mar 02, 2019, 05:38 PM
1-- -----------------------------------------------------
2-- Schema mydb
3-- -----------------------------------------------------
4-- -----------------------------------------------------
5-- Schema test1339
6-- -----------------------------------------------------
7
8-- -----------------------------------------------------
9-- Schema test1339
10-- -----------------------------------------------------
11CREATE SCHEMA IF NOT EXISTS `test1339` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
12-- -----------------------------------------------------
13-- Schema test1337
14-- -----------------------------------------------------
15
16-- -----------------------------------------------------
17-- Schema test1337
18-- -----------------------------------------------------
19CREATE SCHEMA IF NOT EXISTS `test1337` ;
20USE `test1339` ;
21
22-- -----------------------------------------------------
23-- Table `test1339`.`categories`
24-- -----------------------------------------------------
25CREATE TABLE IF NOT EXISTS `test1339`.`categories` (
26 `category_id` INT(11) NOT NULL AUTO_INCREMENT,
27 `category_name` VARCHAR(100) NOT NULL,
28 `parent_id` INT(11) NULL DEFAULT NULL,
29 PRIMARY KEY (`category_id`))
30ENGINE = InnoDB
31AUTO_INCREMENT = 14
32DEFAULT CHARACTER SET = utf8mb4
33COLLATE = utf8mb4_0900_ai_ci;
34
35
36-- -----------------------------------------------------
37-- Table `test1339`.`products`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `test1339`.`products` (
40 `product_id` INT(11) NOT NULL AUTO_INCREMENT,
41 `product_name` VARCHAR(255) NOT NULL,
42 `price` FLOAT NOT NULL,
43 `quantity` INT(11) NOT NULL,
44 `quantity_on_order` INT(11) NULL DEFAULT '0',
45 `category_id` INT(11) NOT NULL DEFAULT '1',
46 `discounted` INT(11) NULL DEFAULT '0',
47 `characteristics` VARCHAR(1000) NULL DEFAULT NULL,
48 `product_image` VARCHAR(500) NULL DEFAULT NULL,
49 PRIMARY KEY (`product_id`),
50 INDEX `categoryid_idx` (`category_id` ASC) VISIBLE,
51 CONSTRAINT `FK_product_category`
52 FOREIGN KEY (`category_id`)
53 REFERENCES `test1339`.`categories` (`category_id`)
54 ON UPDATE CASCADE)
55ENGINE = InnoDB
56AUTO_INCREMENT = 23
57DEFAULT CHARACTER SET = utf8mb4
58COLLATE = utf8mb4_0900_ai_ci;
59
60
61-- -----------------------------------------------------
62-- Table `test1339`.`comments`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `test1339`.`comments` (
65 `id` INT(11) NOT NULL AUTO_INCREMENT,
66 `feedback` VARCHAR(1000) NULL DEFAULT NULL,
67 `product_id` INT(11) NOT NULL,
68 PRIMARY KEY (`id`),
69 INDEX `technomarket_updated` (`product_id` ASC) VISIBLE,
70 CONSTRAINT `FK_comment_product`
71 FOREIGN KEY (`product_id`)
72 REFERENCES `test1339`.`products` (`product_id`))
73ENGINE = InnoDB
74DEFAULT CHARACTER SET = utf8mb4
75COLLATE = utf8mb4_0900_ai_ci;
76
77
78-- -----------------------------------------------------
79-- Table `test1339`.`users`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `test1339`.`users` (
82 `user_id` INT(11) NOT NULL AUTO_INCREMENT,
83 `email` VARCHAR(100) NOT NULL,
84 `password` VARCHAR(60) NOT NULL,
85 `first_name` VARCHAR(50) NOT NULL,
86 `last_name` VARCHAR(50) NOT NULL,
87 `user_role_id` INT(11) NULL DEFAULT '1',
88 `gender` VARCHAR(55) NOT NULL,
89 `verified` TINYINT(11) NULL DEFAULT '0',
90 `image` VARCHAR(500) NULL DEFAULT NULL,
91 PRIMARY KEY (`user_id`),
92 UNIQUE INDEX `email` (`email` ASC) VISIBLE,
93 UNIQUE INDEX `user_id` (`user_id` ASC) VISIBLE,
94 INDEX `userroleid` (`user_role_id` ASC) VISIBLE)
95ENGINE = InnoDB
96AUTO_INCREMENT = 6
97DEFAULT CHARACTER SET = utf8mb4
98COLLATE = utf8mb4_0900_ai_ci;
99
100
101-- -----------------------------------------------------
102-- Table `test1339`.`orders`
103-- -----------------------------------------------------
104CREATE TABLE IF NOT EXISTS `test1339`.`orders` (
105 `order_id` INT(11) NOT NULL AUTO_INCREMENT,
106 `product_id` INT(11) NOT NULL,
107 `user_id` INT(11) NOT NULL,
108 `product_quantity` TINYINT(4) NOT NULL,
109 `status` VARCHAR(50) NULL DEFAULT NULL,
110 `required_date` DATE NULL DEFAULT NULL,
111 `shipped_date` DATE NULL DEFAULT NULL,
112 PRIMARY KEY (`order_id`),
113 INDEX `productid_idx` (`product_id` ASC) VISIBLE,
114 INDEX `userid_idx` (`user_id` ASC) VISIBLE,
115 CONSTRAINT `FK_order_product`
116 FOREIGN KEY (`product_id`)
117 REFERENCES `test1339`.`products` (`product_id`),
118 CONSTRAINT `FK_order_user`
119 FOREIGN KEY (`user_id`)
120 REFERENCES `test1339`.`users` (`user_id`))
121ENGINE = InnoDB
122DEFAULT CHARACTER SET = utf8mb4
123COLLATE = utf8mb4_0900_ai_ci;
124
125
126-- -----------------------------------------------------
127-- Table `test1339`.`store_locations`
128-- -----------------------------------------------------
129CREATE TABLE IF NOT EXISTS `test1339`.`store_locations` (
130 `store_id` INT(11) NOT NULL AUTO_INCREMENT,
131 `store_name` VARCHAR(100) NOT NULL,
132 `city` VARCHAR(100) NOT NULL,
133 `street` VARCHAR(200) NOT NULL,
134 `phone` VARCHAR(15) NOT NULL,
135 `e-mail` VARCHAR(100) NOT NULL,
136 `work_time` VARCHAR(500) NOT NULL,
137 PRIMARY KEY (`store_id`))
138ENGINE = InnoDB
139DEFAULT CHARACTER SET = utf8mb4
140COLLATE = utf8mb4_0900_ai_ci;
141
142
143-- -----------------------------------------------------
144-- Table `test1339`.`user_addresses`
145-- -----------------------------------------------------
146CREATE TABLE IF NOT EXISTS `test1339`.`user_addresses` (
147 `order_details_id` INT(11) NOT NULL AUTO_INCREMENT,
148 `full_name` VARCHAR(150) NOT NULL,
149 `telephone` VARCHAR(20) NOT NULL,
150 `order_id` INT(11) NOT NULL,
151 `comments` VARCHAR(150) NULL DEFAULT NULL,
152 `city_name` VARCHAR(45) NOT NULL,
153 `postal_code` VARCHAR(45) NOT NULL,
154 `street_name` VARCHAR(70) NOT NULL,
155 `street_number` VARCHAR(45) NULL DEFAULT NULL,
156 `block` VARCHAR(45) NULL DEFAULT NULL,
157 `entrance` VARCHAR(45) NULL DEFAULT NULL,
158 `floor` VARCHAR(45) NULL DEFAULT NULL,
159 `apartament` VARCHAR(45) NULL DEFAULT NULL,
160 `user_id` INT(11) NULL DEFAULT NULL,
161 PRIMARY KEY (`order_details_id`),
162 INDEX `ordersid_idx` (`order_id` ASC) VISIBLE,
163 INDEX `useridaddresses_idx` (`user_id` ASC) VISIBLE,
164 CONSTRAINT `FK_user_address_order`
165 FOREIGN KEY (`order_id`)
166 REFERENCES `test1339`.`orders` (`order_id`),
167 CONSTRAINT `FK_user_address_user`
168 FOREIGN KEY (`user_id`)
169 REFERENCES `test1339`.`users` (`user_id`))
170ENGINE = InnoDB
171DEFAULT CHARACTER SET = utf8mb4
172COLLATE = utf8mb4_0900_ai_ci;
173
174
175-- -----------------------------------------------------
176-- Table `test1339`.`user_favorites`
177-- -----------------------------------------------------
178CREATE TABLE IF NOT EXISTS `test1339`.`user_favorites` (
179 `user_id` INT(11) NOT NULL,
180 `product_id` INT(11) NOT NULL,
181 PRIMARY KEY (`user_id`, `product_id`),
182 INDEX `productidfavorites_idx` (`product_id` ASC) VISIBLE,
183 INDEX `useridfavorites` (`user_id` ASC) VISIBLE,
184 CONSTRAINT `FK_user_favorites_product`
185 FOREIGN KEY (`product_id`)
186 REFERENCES `test1337`.`products` (`product_id`)
187 ON UPDATE CASCADE,
188 CONSTRAINT `FK_user_favorites_user`
189 FOREIGN KEY (`user_id`)
190 REFERENCES `test1339`.`users` (`user_id`)
191 ON UPDATE CASCADE)
192ENGINE = InnoDB
193DEFAULT CHARACTER SET = utf8mb4
194COLLATE = utf8mb4_0900_ai_ci;
195
196
197-- -----------------------------------------------------
198-- Table `test1339`.`user_roles`
199-- -----------------------------------------------------
200CREATE TABLE IF NOT EXISTS `test1339`.`user_roles` (
201 `user_role_id` INT(11) NOT NULL AUTO_INCREMENT,
202 `user_role_description` VARCHAR(100) NOT NULL,
203 PRIMARY KEY (`user_role_id`),
204 UNIQUE INDEX `user_role_id` (`user_role_id` ASC) VISIBLE)
205ENGINE = InnoDB
206AUTO_INCREMENT = 3
207DEFAULT CHARACTER SET = utf8mb4
208COLLATE = utf8mb4_0900_ai_ci;
209
210USE `test1337` ;
211
212SET SQL_MODE=@OLD_SQL_MODE;
213SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
214SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;