[+Schema] UPDATE /Desinging Schema Instagram/

 

 

추가적으로 인스타그램을 더 분석하면 이런식으로 된다. 

 

 

 

 

 

 

1️⃣ 엔티티와 속성 (재정의)

 

Primary_key  Foreign_key

역할 설명	Entity	Attribute
사용자	user	user_id, name, email, password, nickname, phone_number, sex, birthday, information, profile_link, profile_image, created_date
게시글	post	post_id, content , location_tag, music, created_date, user_id
댓글	reply	reply_id, comment,  parentid, created_date, post_id, user_id
사진/동영상/shorts	media	media_id, madia_name, post_id
해시태그	hashtag	hashtag_id, tag_data
follower, following은 userid와 매핑 ✔️ follower는 following을 팔로우 함	follow	id, following_id, follower_id
user, post의 like 매핑 테이블 ✔️ user가 post에 좋아요 눌렀다	user_post_like	id, post_id, user_id
user, reply의 like 매핑 테이블 ✔️ user가 reply에 좋아요 눌렀다	user_reply_like	id, reply_id, user_id
post, hashtag의 매핑 테이블  ✔️ post에 hashtag가 사용됐다	post_hashtag	id, hashtag_id,  post_id
user, post의 tag 매핑 테이블 ✔️ post에 tag된 유저이다	user_post_tag	id, post_id, user_id
user, reply의 tag 매핑 테이블 ✔️ reply에 tag된 유저이다	user_reply_tag	id, reply_id, user_id

 

⏺ relations :    user 

user : post = 1 : n
user : reply = 1 : n

user : follow = n : n

 

user : user_post_like = 1: n
user : user_reply_like = 1 : n
user : user_post_tag = 1 : n
user : user_reply_tag = 1 : n

 

 

⏺ relations :    post 

post : reply = 1 : n
post : media = 1 : n
post : post_hashtag = 1 : n
post : user_post_like = 1 : n
post : user_post_tag = 1 : n

 

⏺ relations :    reply

reply : user_reply_like = 1 : n
reply : user_reply_tag = 1 : n

 

 

 

 

2️⃣ update ERD

https://dbdiagram.io/d/638aef14bae3ed7c4544752f

dbdiagram.io - Database Relationship Diagrams Design Tool

 

아래 문법은 dbdiagram에서 다이어그램을 만들기 위해 사용하는 문법?같은건데 대충 복붙해서 느낌만 살리면 누구나 사용할 수 있다.

//01) 기본 entity table

Table User as U {
  user_id int [pk, increment] 
  name varchar(20)
  email varchar(50)
  password varchar(200)
  nickname varchar(20)
  sex enum('MALE','FEMALE', 'INDETERMINATE', 'UNKNOWN')
  birthday DATE
  information text
  profile_link nvarchar(4096) // 일반적으로 웹 서버는 실제 URL의 길이에 대해 상당히 관대한 제한을 설정함(예: 최대 2048자 또는 4096자).
  profile_image blob 
  created_date datetime
}

Table post as P {
  post_id int [pk, increment]
  user_id int //foreign key
  location_tag geography
  music varbianry(max) //DB에 저장하는 것보다는 링크 저장하는게 효율적
  content mediumtext
  created_date datetime
}

Table Reply as R {
  reply_id int [pk, increment] 
  post_id int //foreign key
  user_id int //foreign key
  parent_id int
  comment varchar(255)
  created_date datetime
}


Table Media as M {
  media_id int [pk, increment]
  media_data varbianry(max)
  post_id int //foreign key
}


Table HashTag as HT {
  hashtag_id int [pk, increment]
  tag_data varchar(30)
}


Table Follow as F {
  id int [pk, increment] // auto-increment
  follower_id int
  following_id int
}



//02) many to many middle table
Table Post_HashTag as PH {
  id int [pk, increment] // auto-increment
  hashtag_id int
  post_id int
}

Table User_Post_Like as UPL {
  id int [pk, increment]
  post_id int //foreign key
  user_id int //foreign key
}

Table User_Reply_Like as URL {
  id int [pk, increment]
  reply_id int //foreign key
  user_id int //foreign key
}

Table User_Post_Tag as UPT {
  id int [pk, increment]
  post_id int //foreign key
  user_id int //foreign key
}

Table User_Reply_Tag as URT {
  id int [pk, increment]
  reply_id int //foreign key
  user_id int //foreign key
}




// > many-to-one; 
// < one-to-many; 
// - one-to-one; 
// <> many-to-many


//Relation 
//01_User
Ref: U.user_id < P.user_id
Ref: U.user_id < R.user_id

Ref: U.user_id < F.following_id
Ref: U.user_id < F.follower_id

Ref: U.user_id < UPT.user_id
Ref: U.user_id < UPL.user_id
Ref: U.user_id < URT.user_id
Ref: U.user_id < URL.user_id


//02_Post
Ref: P.post_id < R.post_id
Ref: P.post_id < M.post_id
Ref: P.post_id < UPT.post_id
Ref: P.post_id < UPL.post_id

Ref: P.post_id < PH.post_id
Ref: HT.hashtag_id < PH.hashtag_id

//03_Reply
Ref: R.reply_id < URT.reply_id
Ref: R.reply_id < URL.reply_id

 

 

3️⃣ SQL 추출

 

mysql로 추출했다.

CREATE TABLE `User` (
  `user_id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(20),
  `email` varchar(50),
  `password` varchar(200),
  `nickname` varchar(20),
  `sex` enum,
  `birthday` DATE,
  `information` text,
  `profile_link` nvarchar(4096),
  `profile_image` blob,
  `created_date` datetime
);

CREATE TABLE `post` (
  `post_id` int PRIMARY KEY AUTO_INCREMENT,
  `user_id` int,
  `location_tag` geography,
  `music` varbianry(max),
  `content` mediumtext,
  `created_date` datetime
);

CREATE TABLE `Reply` (
  `reply_id` int PRIMARY KEY AUTO_INCREMENT,
  `post_id` int,
  `user_id` int,
  `parent_id` int,
  `comment` varchar(255),
  `created_date` datetime
);

CREATE TABLE `Media` (
  `media_id` int PRIMARY KEY AUTO_INCREMENT,
  `media_data` varbianry(max),
  `post_id` int
);

CREATE TABLE `HashTag` (
  `hashtag_id` int PRIMARY KEY AUTO_INCREMENT,
  `tag_data` varchar(30)
);

CREATE TABLE `Follow` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `follower_id` int,
  `following_id` int
);

CREATE TABLE `Post_HashTag` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `hashtag_id` int,
  `post_id` int
);

CREATE TABLE `User_Post_Like` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `post_id` int,
  `user_id` int
);

CREATE TABLE `User_Reply_Like` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `reply_id` int,
  `user_id` int
);

CREATE TABLE `User_Post_Tag` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `post_id` int,
  `user_id` int
);

CREATE TABLE `User_Reply_Tag` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `reply_id` int,
  `user_id` int
);


ALTER TABLE `post` ADD FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `Reply` ADD FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `Follow` ADD FOREIGN KEY (`following_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `Follow` ADD FOREIGN KEY (`follower_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `User_Post_Tag` ADD FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `User_Post_Like` ADD FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `User_Reply_Tag` ADD FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `User_Reply_Like` ADD FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`);

ALTER TABLE `Reply` ADD FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`);

ALTER TABLE `Media` ADD FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`);

ALTER TABLE `User_Post_Tag` ADD FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`);

ALTER TABLE `User_Post_Like` ADD FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`);

ALTER TABLE `Post_HashTag` ADD FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`);

ALTER TABLE `Post_HashTag` ADD FOREIGN KEY (`hashtag_id`) REFERENCES `HashTag` (`hashtag_id`);

ALTER TABLE `User_Reply_Tag` ADD FOREIGN KEY (`reply_id`) REFERENCES `Reply` (`reply_id`);

ALTER TABLE `User_Reply_Like` ADD FOREIGN KEY (`reply_id`) REFERENCES `Reply` (`reply_id`);

 

 

 

 

 

---

 

 

 

나중에봐야지 

 

https://velog.io/@jcinsh/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%8A%A4%ED%83%80%EB%B2%85%EC%8A%A4-%EA%B3%BC%EC%A0%9C

 

https://velog.io/@doryyy/TIL-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%AA%A8%EB%8D%B8%EB%A7%81