DatabaseProject5200/schema.sql at master · BenWng/DatabaseProject5200 · GitHub

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
create table Users(
  id int primary key auto_increment,
  email varchar(48),
  name varchar(48) not null,
  password varchar(200) not null,
  shippingAddress varchar(100),
  seller boolean,
  shopName varchar(200),
  admin boolean
);

create table CategoryType(
  name varchar(48) primary key
);

create table ProductsSold(
  id int primary key auto_increment,
  name varchar(48) not null,
  price int not null,
  shortDescription varchar(1000) not null,
  longDescription varchar(1000) not null,
  sellerId int not null,
  foreign key (sellerId) references Users(id)
  on update cascade
  on delete cascade,
  purchaserId int not null,
  foreign key (purchaserId) references Users(id)
  on update cascade
  on delete cascade,
  category varchar(48),
  foreign key (category) references CategoryType(name)
  on update cascade
  on delete no action,
  shipped boolean,
  received boolean,
  pictureURL varchar(255)
);

create table ProductsSelling(
  id int primary key auto_increment,
  name varchar(48) not null,
  price int not null,
  shortDescription varchar(1000) not null,
  longDescription varchar(1000) not null,
  sellerId int not null,
  foreign key (sellerId) references Users(id)
  on update cascade
  on delete cascade,
  category varchar(48),
  foreign key (category) references CategoryType(name)
  on update cascade
  on delete no action,
  quantity int,
  pictureURL varchar(255)
);

create table FollowedUsers(
  followerId int,
  followedId int,
  primary key (followerId, followedId)
  );

create table PicturesSold(
  productId int primary key,
  picture longblob,
  foreign key (productId) references ProductsSold(id)
  on update cascade
  on delete cascade
  );

create table PicturesSelling(
  productId int primary key,
  picture longblob,
  foreign key (productID) references ProductsSelling(id)
  on update cascade
  on delete cascade
  );

create table Wishlist(
  userId int not null,
  productId int not null,
  foreign key (userId) references Users(id)
  on update cascade
  on delete cascade,
  foreign key (productId) references ProductsSelling(id)
  on update cascade
  on delete cascade,
  primary key(userId, productId)
  );

create table ShopCategories(
  sellerId int,
  category varchar(200),
  foreign key(sellerId) references Users(id)
  on update cascade
  on delete cascade,
  primary key(sellerId, category)
  );