· 6 years ago · Mar 13, 2019, 04:36 PM
1CREATE SCHEMA IF NOT EXISTS `test1339` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
2USE `test1339` ;
3
4-- -----------------------------------------------------
5-- Table `test1339`.`categories`
6-- -----------------------------------------------------
7CREATE TABLE IF NOT EXISTS `test1339`.`categories` (
8 `category_id` INT(11) NOT NULL AUTO_INCREMENT,
9 `category_name` VARCHAR(100) NOT NULL,
10 `parent_id` INT(11) NULL DEFAULT '0',
11 PRIMARY KEY (`category_id`))
12ENGINE = InnoDB
13AUTO_INCREMENT = 21
14DEFAULT CHARACTER SET = utf8mb4
15COLLATE = utf8mb4_0900_ai_ci;
16
17
18-- -----------------------------------------------------
19-- Table `test1339`.`ordered_products`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `test1339`.`ordered_products` (
22 `order_id` INT(11) NOT NULL,
23 `product_id` INT(11) NOT NULL,
24 `quantity` INT(11) NULL DEFAULT '1',
25 PRIMARY KEY (`order_id`, `product_id`),
26 INDEX `FK_FK_FK_idx` (`order_id` ASC) VISIBLE,
27 INDEX `FK_PK_FK_idx` (`product_id` ASC) VISIBLE)
28ENGINE = InnoDB
29DEFAULT CHARACTER SET = utf8mb4
30COLLATE = utf8mb4_0900_ai_ci;
31
32
33-- -----------------------------------------------------
34-- Table `test1339`.`orders`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `test1339`.`orders` (
37 `order_id` INT(11) NOT NULL AUTO_INCREMENT,
38 `user_id` INT(11) NOT NULL,
39 `quantity` INT(11) NOT NULL DEFAULT '1',
40 `address` VARCHAR(1000) NOT NULL,
41 `status` VARCHAR(60) NULL DEFAULT 'Not shipped',
42 `required_date` VARCHAR(300) NULL DEFAULT NULL,
43 `shipped_date` VARCHAR(300) NULL DEFAULT NULL,
44 `date_made` VARCHAR(45) NOT NULL,
45 PRIMARY KEY (`order_id`))
46ENGINE = InnoDB
47AUTO_INCREMENT = 91
48DEFAULT CHARACTER SET = utf8mb4
49COLLATE = utf8mb4_0900_ai_ci;
50
51
52-- -----------------------------------------------------
53-- Table `test1339`.`product_images`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `test1339`.`product_images` (
56 `image_id` INT(11) NOT NULL AUTO_INCREMENT,
57 `image_name` VARCHAR(1000) NULL DEFAULT NULL,
58 `product_id` INT(11) NULL DEFAULT NULL,
59 PRIMARY KEY (`image_id`))
60ENGINE = InnoDB
61AUTO_INCREMENT = 6
62DEFAULT CHARACTER SET = utf8mb4
63COLLATE = utf8mb4_0900_ai_ci;
64
65
66-- -----------------------------------------------------
67-- Table `test1339`.`products`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `test1339`.`products` (
70 `product_id` INT(11) NOT NULL AUTO_INCREMENT,
71 `product_name` VARCHAR(255) NOT NULL,
72 `price` FLOAT NOT NULL,
73 `quantity` INT(11) NOT NULL,
74 `quantity_on_order` INT(11) NULL DEFAULT '0',
75 `category_id` INT(11) NOT NULL DEFAULT '1',
76 `discounted` FLOAT NULL DEFAULT '0',
77 `brand` VARCHAR(45) NULL DEFAULT NULL,
78 `inches` FLOAT NULL DEFAULT NULL,
79 `frequency` INT(11) NULL DEFAULT NULL,
80 `kw` INT(11) NULL DEFAULT NULL COMMENT 'null',
81 `number_of_hobs` INT(11) NULL DEFAULT NULL,
82 `timer` INT(11) NULL DEFAULT NULL,
83 `camera_pixels` FLOAT NULL DEFAULT NULL,
84 `finger_print` INT(11) NULL DEFAULT NULL,
85 `water_proof` INT(11) NULL DEFAULT NULL,
86 `kg_capacity` INT(11) NULL DEFAULT NULL,
87 `intelligent_wash` INT(11) NULL DEFAULT NULL,
88 `inbuild_dryer` INT(11) NULL DEFAULT NULL,
89 PRIMARY KEY (`product_id`),
90 INDEX `categoryid_idx` (`category_id` ASC) VISIBLE,
91 CONSTRAINT `FK_product_category`
92 FOREIGN KEY (`category_id`)
93 REFERENCES `test1339`.`categories` (`category_id`))
94ENGINE = InnoDB
95AUTO_INCREMENT = 42
96DEFAULT CHARACTER SET = utf8mb4
97COLLATE = utf8mb4_0900_ai_ci;
98
99
100-- -----------------------------------------------------
101-- Table `test1339`.`user_favorites`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `test1339`.`user_favorites` (
104 `user_id` INT(11) NOT NULL,
105 `product_id` INT(11) NOT NULL,
106 PRIMARY KEY (`user_id`, `product_id`),
107 INDEX `useridfavorites` (`user_id` ASC) VISIBLE)
108ENGINE = InnoDB
109DEFAULT CHARACTER SET = utf8mb4
110COLLATE = utf8mb4_0900_ai_ci;
111
112
113-- -----------------------------------------------------
114-- Table `test1339`.`user_orders`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `test1339`.`user_orders` (
117 `order_id` INT(11) NOT NULL,
118 `user_id` VARCHAR(45) NOT NULL,
119 PRIMARY KEY (`order_id`, `user_id`))
120ENGINE = InnoDB
121DEFAULT CHARACTER SET = utf8mb4
122COLLATE = utf8mb4_0900_ai_ci;
123
124
125-- -----------------------------------------------------
126-- Table `test1339`.`user_roles`
127-- -----------------------------------------------------
128CREATE TABLE IF NOT EXISTS `test1339`.`user_roles` (
129 `user_role_id` INT(11) NOT NULL AUTO_INCREMENT,
130 `user_role_description` VARCHAR(100) NOT NULL,
131 PRIMARY KEY (`user_role_id`),
132 UNIQUE INDEX `user_role_id` (`user_role_id` ASC) VISIBLE)
133ENGINE = InnoDB
134AUTO_INCREMENT = 3
135DEFAULT CHARACTER SET = utf8mb4
136COLLATE = utf8mb4_0900_ai_ci;
137
138
139-- -----------------------------------------------------
140-- Table `test1339`.`users`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `test1339`.`users` (
143 `user_id` INT(11) NOT NULL AUTO_INCREMENT,
144 `email` VARCHAR(100) NOT NULL,
145 `password` VARCHAR(60) NOT NULL,
146 `first_name` VARCHAR(50) NOT NULL,
147 `last_name` VARCHAR(50) NOT NULL,
148 `user_role_id` INT(11) NULL DEFAULT '1',
149 `gender` VARCHAR(55) NOT NULL,
150 `verified` TINYINT(11) NULL DEFAULT '0',
151 `subscribed` INT(11) NULL DEFAULT '0',
152 PRIMARY KEY (`user_id`),
153 UNIQUE INDEX `email` (`email` ASC) VISIBLE,
154 UNIQUE INDEX `user_id` (`user_id` ASC) VISIBLE,
155 INDEX `userroleid` (`user_role_id` ASC) VISIBLE)
156ENGINE = InnoDB
157AUTO_INCREMENT = 24
158DEFAULT CHARACTER SET = utf8mb4
159COLLATE = utf8mb4_0900_ai_ci;
160
161
162SET SQL_MODE=@OLD_SQL_MODE;
163SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
164SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;