-
Notifications
You must be signed in to change notification settings - Fork 1
/
main.sql
110 lines (93 loc) · 3.22 KB
/
main.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
CREATE DATABASE IF NOT EXISTS price_observer CHARACTER SET utf8 COLLATE utf8_general_ci;
USE price_observer;
CREATE TABLE IF NOT EXISTS `product`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`description` TEXT,
`properties_id` INT,
`model` VARCHAR(255),
`manufacturer_id` INT NOT NULL,
`year` YEAR,
`image` TEXT,
`product_type_id` INT NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `product_properties`
(
`id` INT NOT NULL AUTO_INCREMENT,
`properties` TEXT, # A json presentation of product properties
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `manufacturer`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`country` VARCHAR(70),
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `user`
(
`id` INT NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(50) NOT NULL,
`last_name` VARCHAR(50) NOT NULL,
`email` VARCHAR(70) NOT NULL,
`birth` DATE NOT NULL,
`password` VARCHAR(68) NOT NULL,
`role_id` TINYINT NOT NULL,
`profile_image` TEXT, #A link to the profile image (local storage).
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `user_role`
(
`id` TINYINT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `wish_product`
(
`id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT NOT NULL,
`product_id` INT NOT NULL,
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
`date_added` DATE NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `product_type`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `store`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`url` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `product_price`
(
`id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NOT NULL,
`store_id` INT NOT NULL,
`price` FLOAT NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (`id`)
);
ALTER TABLE `product`
ADD CONSTRAINT `product_fk0` FOREIGN KEY (`properties_id`) REFERENCES `product_properties` (`id`);
ALTER TABLE `product`
ADD CONSTRAINT `product_fk1` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturer` (`id`);
ALTER TABLE `product`
ADD CONSTRAINT `product_fk2` FOREIGN KEY (`product_type_id`) REFERENCES `product_type` (`id`);
ALTER TABLE `user`
ADD CONSTRAINT `user_fk0` FOREIGN KEY (`role_id`) REFERENCES `user_role` (`id`);
ALTER TABLE `wish_product`
ADD CONSTRAINT `wish_product_fk0` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
ALTER TABLE `wish_product`
ADD CONSTRAINT `wish_product_fk1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);
ALTER TABLE `product_price`
ADD CONSTRAINT `product_price_fk0` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);
ALTER TABLE `product_price`
ADD CONSTRAINT `product_price_fk1` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`);