📝📌📒
Table users {
id int [pk, increment] // auto-increment
email varchar(100) [unique, not null]
nickname varchar(50)
password varchar(300) [not null]
profile_image varchar(3000)
created_at datetime [default: `now()`]
}
Table postings {
id int [pk, increment] // auto-increment
user_id int [not null]
contents varchar(2000) [null]
created_at datetime [default: `now()`]
}
Ref: postings.user_id > users.id
Table posting_images {
id int [pk, increment]
posting_id int [not null]
image_url varchar(3000)
created_at datetime [default: `now()`]
}
Ref: posting_images.posting_id > postings.id
Table comments {
id int [pk, increment]
comment varchar(2000)
posting_id int [not null]
user_id int [not null]
created_at datetime [default: `now()`]
}
Ref: comments.posting_id > postings.id
Ref: comments.user_id > users.id
위 의 테이블과 컬럼의 세부 속성에 따라 users, postings, posting_images, comments 네 가지 테이블을 아래와 같이 만들어 주었다.
CREATE TABLE POSTINGS # postings 테이블 작성
(
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
contents VARCHAR(2000),
created_at DATETIME DEFAULT CURRNET_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id)
);
select users.nickname, users.profile_image,
postings.contents, comments.comment, posting_images.image_url from users
join postings on postings.user_id = users.id
join comments on comments.posting_id = postings.id
join posting_images on posting_images.posting_id = postings.id
where users.id = '1';