[5주차 복습 발표] DB 설계 및 실습

 

 

DBMS vs RDBMS

DBMS

데이터베이스를 생성, 저장, 관리할 수 있는 기능들을 제공하는 시스템(프로그램)이다.

 

RDBMS

데이터 간에 관계를 가지고 있는 DBMS이다.  데이터 간에 관계를 맺고 있기 때문에 찾고 싶은 데이터가 내가 지금 보고 있는 곳에 없더라도 거기에 있는 힌트를 사용해서 원하는 것을 찾아낼 수 있다.

데이터 중복·불일치를 최소화할 수 있고, 데이터의 보안, 일관성·무결성 유지의 장점이 있다.

 

비교

	DBMS	RDBMS
저장 형식	파일(계층적, 탐색 형식)	테이블(PK 식별자 사용)
정규화 여부	X	O
데이터 보안	X	O
적합한 데이터 처리 양	작음	큼
예시	파일시스템, XML	MySQL, Oracle

 

 

MySQL DATETIME vs TIMESTAMP

두 자료형 모두 날짜와 시간을 표현해 주는 자료형이고, 포맷 형식도 동일하다.

가장 큰 차이점은 데이터 저장 방식이다. TIMESTAMP는 UTC(협정 세계시)로 저장한다. 

우리나라는 협정 세계시보다 9시간 빠르다(UTC+9). 우리나라에서 오후 9시에 글을 작성하고, UTC 기준 국가 중 하나인 영국에서 그 게시물을 본다고 가정해 보자. DATETIME을 사용하면 오후 9시로 보이고, TIMESTAMP를 사용하면 오후 12시로 보인다. 따라서 글로벌 서비스를 제공한다면 TIMESTAMP를 사용해야 한다.

 

 

테이블 설계

기본키 (PK)

기본키(Primary Key)는 테이블의 각 행을 유니크하게 구별할 수 있는 key 값을 말한다.

게시글 번호를 제외한 모든 컬럼은 중복될 가능성이 있어서 기본키가 될 수 없다.

따라서 작성된 게시글 순서대로 번호를 부여하는 게시글 번호 칼럼이 기본키이다.

정규화

정규화는 정해진 규칙에 따라 테이블을 분리하는 과정을 말한다.

위의 테이블에서 작성자 '최씨'가 직업을 수정했다고 해보자. 그러면 우리가 직접 3행과 4행의 직업 컬럼을 수정해줘야 한다. 지금은 2행만 바꿔주면 되지만 작성자가 많은 글을 작성했을 수도 있다. 이런 경우 일일이 바꿔주는 게 힘들기 때문에 테이블을 분리한다.

 

사용자 테이블 PK

현재 사용자 테이블에는 각 행을 구분할 수 있는 유니크한 컬럼이 없다. 따라서 사용자 ID라는 컬럼을 생성하여 기본키로 설정한다.

 

게시글 테이블 FK

게시글 테이블에서 특정 값을 가지고 사용자 테이블의 정보를 조회할 수 있어야 한다. 따라서 게시글 테이블에 작성자 ID 컬럼을 추가하고, 이 컬럼이 사용자 테이블의 작성자 ID를 참조하게 한다.

그러면 게시글 테이블에서 작성자 ID를 참고하여 사용자 테이블에 있는 해당 작성자의 이름, 직업, 생년월일을 알 수 있을 것이다.

 

이렇게 A 테이블에서 B 테이블의 데이터를 찾아가고 싶을 때 사용하는 Key를 외래키라고 하며, 이 예시에서는 작성자ID가 외래키에 해당한다. B 테이블의 기본키를 A 테이블의 외래키로 사용하는 것이 이상적이다.

 

 

테이블 구현

사용자 테이블 생성

작성자 id는 기본키이기 때문에 자동으로 1씩 증가하도록 AUTO_INCREMENT와 PRIMARY KEY 옵션을 넣어준다.

참고로 AUTO_INCREMENT의 기본 설정으로는 데이터 삽입에 실패해도 값이 증가한다. 값이 증가하지 않게 하려면 auto_increment_lock_mode 설정을 변경하면 된다.

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    job VARCHAR(100),
    birth DATE
);

 

게시글 테이블 생성

게시글 번호가 기본키이기 때문에 마찬가지로 AUTO_INCREMENT와 PRIMARY KEY 옵션을 넣어준다.

updated_at 컬럼은 수정일자를 나타내는 컬럼이고, 'ON UPDATE NOW()'가 이 행이 업데이트된다면 현재 시간을 반영하겠다는 의미를 가지고 있다.

게시글 테이블의 user_id 컬럼이 사용자 테이블의 id 컬럼을 참조하는 외래키 이므로 foreign key와 references에 각 테이블과 컬럼명을 작성한다.

CREATE TABLE posts(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content VARCHAR(2000),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
    user_id INT,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

 

JOIN

분리되어있는 테이블을 위 그림처럼 합쳐보자.

JOIN은 데이터베이스 내의 여러 테이블을 붙여서 가상의 테이블을 만들어 돌려주는 기능이다.

어떤 컬럼을 기준으로 테이블을 붙일지는 ON 절에 작성한다.

LEFT JOIN은 왼쪽 테이블 값은 모두 가져오고, 오른쪽 테이블 값은 교집합이 존재하는 데이터만 가져온다는 뜻이다.

SELECT posts.id, title, content, created_at, updated_at, name, job, birth
FROM posts
LEFT JOIN users
ON posts.user_id = users.id;