-
Notifications
You must be signed in to change notification settings - Fork 0
/
instagram_clone.sql
86 lines (78 loc) · 2.82 KB
/
instagram_clone.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
-- İnstagram Clone database : USER AND PHOTOS tables then their relations
-- user table informations
CREATE TABLE users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- id will be given by system automatically
INSERT INTO users(username)
VALUES ('Tobias_Müller'),('JOHN_Fb_1907'),('a.esraguler');
-- see conclusion
select * from instagram_clone.users;
-- instagram actually exist for sharing photos with friends right? let's introduce our photos to system
CREATE TABLE photos (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
image_url VARCHAR(500) NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id)
);
INSERT INTO photos (image_url,user_id)
VALUES ('https://www.flickr.com/pho/',1),
('https://www.flic',2),
('https://www.flickr.com',3);
select * from photos;
-- comments and likes
CREATE TABLE comments (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
comment_text VARCHAR(255) NOT NULL,
photo_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(photo_id) REFERENCES photos(id),
FOREIGN KEY(user_id) REFERENCES users(id)
);
INSERT INTO comments (comment_text,user_id,photo_id)
VALUES('You look amazing',2,1),
('du siehst sehr schön aus',3,1),
('güzel görünüyorsun',2,2);
select* from comments;
CREATE TABLE likes (
user_id INTEGER NOT NULL,
photo_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(photo_id) REFERENCES photos(id),
PRIMARY KEY(user_id, photo_id)
);
select *from likes;
-- Fallows fully connect with follower and and fallowee and thir id can not be null
CREATE TABLE follows (
follower_id INTEGER NOT NULL,
followee_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(follower_id) REFERENCES users(id),
FOREIGN KEY(followee_id) REFERENCES users(id),
PRIMARY KEY(follower_id, followee_id)
);
select *from follows;
-- tags help us get our content in front of more people here we need a id too like 1 2 3...
CREATE TABLE tags (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
tag_name VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
select *from tags;
CREATE TABLE photo_tags (
photo_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
FOREIGN KEY(photo_id) REFERENCES photos(id),
FOREIGN KEY(tag_id) REFERENCES tags(id),
PRIMARY KEY(photo_id, tag_id)
);
select *from photo_tags;
INSERT INTO likes(user_id,photo_id) VALUES (1, 1), (1, 2), (1, 3);
INSERT INTO follows(follower_id, followee_id) VALUES (1, 1), (1, 3), (2, 1);
INSERT INTO tags(tag_name) VALUES ('sunset'), ('photography'), ('sunrise');
INSERT INTO photo_tags(photo_id, tag_id) VALUES (1, 3), (3, 2), (2, 3);