-
Notifications
You must be signed in to change notification settings - Fork 0
/
YeOldeStockTrader.sql
59 lines (51 loc) · 2.01 KB
/
YeOldeStockTrader.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
DROP DATABASE IF EXISTS YeOldeStockTrader;
CREATE DATABASE YeOldeStockTrader;
USE YeOldeStockTrader;
CREATE TABLE User (
userID VARCHAR(25) PRIMARY KEY, -- username = userID
email VARCHAR(25) NOT NULL,
password VARCHAR(32) NOT NULL, -- password is MD5 hashed
firstName VARCHAR(25) NOT NULL,
lastName VARCHAR(25) NOT NULL,
profilePicture VARCHAR(50)
);
CREATE TABLE Post (
postID int(10) PRIMARY KEY AUTO_INCREMENT,
stockName VARCHAR(25) NOT NULL,
direction VARCHAR(1000) NOT NULL,
ticker VARCHAR(10) NOT NULL,
date VARCHAR(11) NOT NULL,
time VARCHAR(11) NOT NULL,
userID VARCHAR(25) NOT NULL,
category VARCHAR(25) NOT NULL,
CONSTRAINT fk_user FOREIGN KEY (userID) REFERENCES User(userID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Answer (
answerID int(10) PRIMARY KEY AUTO_INCREMENT,
response VARCHAR(1000) NOT NULL,
date VARCHAR(11) NOT NULL,
time VARCHAR(11) NOT NULL,
postID int(10) NOT NULL,
userID VARCHAR(25) NOT NULL,-- , -- author
CONSTRAINT foreign key fk1(postID) references Post(postID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT foreign key fk2(userID) references User(userID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Answer_Rating (
upvote int(255),
downvote int(255),
userID VARCHAR(25) NOT NULL,
answerID int(10) NOT NULL,
CONSTRAINT foreign key fk1(userID) references User(userID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT foreign key fk2(answerID) references Answer(answerID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Bookmark(
postID int(10) NOT NULL,
userID VARCHAR(25) NOT NULL,-- , -- author
CONSTRAINT foreign key fk1(postID) references Post(postID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT foreign key fk2(userID) references User(userID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Overall_Rating(
answerID int(10) NOT NULL,
rating int(10) NOT NULL,
CONSTRAINT foreign key fk1(answerID) references Answer(answerID) ON DELETE CASCADE ON UPDATE CASCADE
);