-
Notifications
You must be signed in to change notification settings - Fork 1
/
creazioneDatabase.txt
114 lines (95 loc) · 2.99 KB
/
creazioneDatabase.txt
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
111
112
113
114
CREATE TABLE Users (
UserID INT PRIMARY KEY AUTO_INCREMENT,
Username VARCHAR(50) NOT NULL,
Password VARCHAR(255) NOT NULL,
Email VARCHAR(100) NOT NULL,
Bio TEXT,
RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY AUTO_INCREMENT,
CategoryName VARCHAR(50) NOT NULL
);
CREATE TABLE Posts (
PostID INT PRIMARY KEY AUTO_INCREMENT,
UserID INT,
MediaURL VARCHAR(255),
Caption TEXT,
PostDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CategoryID INT,
FOREIGN KEY (UserID) REFERENCES Users(UserID),
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);
CREATE TABLE Followers (
FollowerID INT PRIMARY KEY AUTO_INCREMENT,
FollowerUserID INT,
FollowingUserID INT,
FollowDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (FollowerUserID) REFERENCES Users(UserID),
FOREIGN KEY (FollowingUserID) REFERENCES Users(UserID)
);
CREATE TABLE Comments (
CommentID INT PRIMARY KEY AUTO_INCREMENT,
PostID INT,
UserID INT,
CommentText TEXT,
CommentDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (PostID) REFERENCES Posts(PostID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE TABLE ChatMessages (
MessageID INT PRIMARY KEY AUTO_INCREMENT,
SenderID INT,
ReceiverID INT,
MessageText TEXT,
SendDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (SenderID) REFERENCES Users(UserID),
FOREIGN KEY (ReceiverID) REFERENCES Users(UserID)
);
CREATE TABLE Notifications (
NotificationID INT PRIMARY KEY AUTO_INCREMENT,
fromUserID INT,
toUserID INT,
NotificationText TEXT,
PostID INT DEFAULT NULL,
NotificationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (fromUserID) REFERENCES Users(UserID),
FOREIGN KEY (toUserID) REFERENCES Users(UserID),
FOREIGN KEY (PostID) REFERENCES Posts(PostID)
);
CREATE TABLE UserInfos (
UserID INT PRIMARY KEY,
LogoURL VARCHAR(255) NOT NULL,
FullName VARCHAR(100) NOT NULL,
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE TABLE Likes (
LikeID INT PRIMARY KEY AUTO_INCREMENT,
PostID INT,
UserID INT,
LikeDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (PostID) REFERENCES Posts(PostID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE TABLE Tagged (
TaggedID INT PRIMARY KEY AUTO_INCREMENT,
PostID INT,
UserID INT,
TagDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (PostID) REFERENCES Posts(PostID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE TABLE Saved (
SavedID INT PRIMARY KEY AUTO_INCREMENT,
PostID INT,
UserID INT,
SaveDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (PostID) REFERENCES Posts(PostID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE TABLE Images (
ImageID INT PRIMARY KEY AUTO_INCREMENT,
ImageName VARCHAR(255) NOT NULL,
ImageData LONGBLOB,
UploadDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);