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
|
create schema if not exists webs;
create table if not exists webs.category (
`id` int not null auto_increment,
`name` varchar(45) not null,
`parent` int null default null,
primary key (`id`),
constraint `category_parent_fk`
foreign key (`parent`)
references webs.category (`id`)
on update cascade
);
create table if not exists webs.product (
`id` int not null auto_increment,
`name` varchar(255) not null,
`price` decimal(5, 2) not null,
`image` boolean not null default false,
`description` mediumtext null default null,
`category` int not null,
primary key (`id`),
constraint `product_category_fk`
foreign key (`category`)
references webs.category (`id`)
on update cascade
);
create table if not exists webs.user (
`id` int not null auto_increment,
`name` varchar(45) not null,
`hash` binary(64) not null,
`privileges` int not null default 1,
primary key (`id`)
);
create table if not exists webs.order (
`id` int not null auto_increment,
`status` int not null default 1,
`user` int not null,
primary key (`id`),
constraint `order_user_fk`
foreign key (`user`)
references webs.user (`id`)
on update cascade
);
create table if not exists webs.orderproduct (
`id` int not null auto_increment,
`product` int not null,
`count` int not null default 1,
`order` int not null,
primary key (`id`),
constraint `cart_product_fk`
foreign key (`product`)
references webs.product (`id`)
on update cascade,
constraint `cart_order_fk`
foreign key (`order`)
references webs.order (`id`)
on update cascade
);
create table if not exists webs.promotion (
`id` int not null auto_increment,
`product` int not null,
`count_buff` int not null default 1,
`price_buff` decimal(4, 3) not null default 1.0,
primary key (`id`),
constraint `promotion_product_fk`
foreign key (`product`)
references webs.product (`id`)
on update cascade
);
|