ERD 다이어그램 활용하기

https://dbdiagram.io/d

dbdiagram.io - Database Relationship Diagrams Design Tool

 

• ERD 프로그램에 넣을 코드 생성법

show create table dept_emp;

 

CREATE TABLE dept_emp (
   emp_no int NOT NULL,
   dept_no char(4) NOT NULL,
   from_date date NOT NULL,
   to_date date NOT NULL,
   PRIMARY KEY (emp_no,dept_no),
   KEY emp_no (emp_no),
   KEY dept_no (dept_no),
   CONSTRAINT dept_emp_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
   CONSTRAINT dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
 )
 
CREATE TABLE departments (
   dept_no char(4) NOT NULL,
   dept_name varchar(40) NOT NULL,
   PRIMARY KEY (dept_no),
   UNIQUE KEY dept_name (dept_name)
 )
 
CREATE TABLE dept_manager (
   dept_no char(4) NOT NULL,
   emp_no int NOT NULL,
   from_date date NOT NULL,
   to_date date NOT NULL,
   PRIMARY KEY (emp_no,dept_no),
   KEY emp_no (emp_no),
   KEY dept_no (dept_no),
   CONSTRAINT dept_manager_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
   CONSTRAINT dept_manager_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
 )
 
CREATE TABLE employees (
   emp_no int NOT NULL,
   birth_date date NOT NULL,
   first_name varchar(14) NOT NULL,
   last_name varchar(16) NOT NULL,
   gender enum('M','F') NOT NULL,
   hire_date date NOT NULL,
   PRIMARY KEY (emp_no)
 ) 
 
CREATE TABLE salaries (
   emp_no int NOT NULL,
   salary int NOT NULL,
   from_date date NOT NULL,
   to_date date NOT NULL,
   PRIMARY KEY (emp_no,from_date),
   KEY emp_no (emp_no),
   CONSTRAINT salaries_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
 ) 
 
CREATE TABLE titles (
   emp_no int NOT NULL,
   title varchar(50) NOT NULL,
   from_date date NOT NULL,
   to_date date DEFAULT NULL,
   PRIMARY KEY (emp_no,title,from_date),
   KEY emp_no (emp_no),
   CONSTRAINT titles_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
 )

 

• 다음 테이블 간의 관계를 알아보자.
• employees와 departments는 N:M 관계이고, 그 외의 관계는 1:N 관계이다.

---

• 블로그 테이블 설계하기

-- 블로그 table설계

create table user(
    user_id int primary key auto_increment,
    user_name varchar(20) not null unique,
    email varchar(30) not null unique,
    password varchar(20) not null
);

create table post(
	post_id int primary key,
    user_name varchar(20) not null,
    title varchar(50),
    content text not null,
    posted_time timestamp default current_timestamp,
    foreign key(user_name) references `user`(user_name)
);

create table reply(
    user_name varchar(20) not null,
    content text not null,
    posted_time timestamp default current_timestamp,
    foreign key(user_name) references user(user_name)
);

create table liked_post(
    user_id int,
    post_id int,
    foreign key (user_id) references user(user_id),
    foreign key (post_id) references post(post_id)
);