[ 살펴보기 ] RDB - Relationships

One to One Relationship

One to One relationship은 특정 table의 data row 하나가 다른 table의 data row 하나와 relationship을 형성하는 relationship type을 말한다. 다음 예제를 살펴보자.

User table

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(30),
  email VARCHAR(30),
  PRIMARY KEY(id)
)

UserProfile table

CREATE TABLE user_profiles (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  address VARCHAR(30),
  phone VARCHAR(15),
  user_id INTEGER UNIQUE,
  PRIMARY KEY(id),
  CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(id)
)

위의 예제에서 One To One relationship을 형성하기 위해 foreign key와 unique constraint을 사용하고 있다. 그 결과 user_profiles table에선 중복된 user id data row를 가질 수 없기에 user_profiles table의 각 data row는 user table의 data row와 one to one relationship을 형성한다.

users table 입장에서도 자신의 profile data는 user profile에 하나만 존재할 수 있고 user_profile table 입장에서도 users table data 중 하나의 data와 relationship을 맺게 되는 것이다. 위와 같이 생성한 table에 data를 추가하는 예제를 살펴보자.

INSERT INTO users (name, email) VALUES ('jake', 'jake@email.com');
/*
id  name  email
1   jake  jake@email.com
*/
INSERT INTO user_profiles (user_id, address, phone) VALUES (1, 'New York', '123-456-7890');

위의 예제에서 살펴볼 수 있듯이 user_profiles table의 user_id column은 user table id를 reference하는 foreign key constraint이 설정되어 있기에 data를 추가할 때 users table에 존재하는 user id data를 설정하여 추가해야 하며 UNIQUE constraint으로 인해 user_profiles table에 새로운 data를 추가할 때 이미 존재하는 id를 추가할 수 없다.

이렇게 One to One relationship이 형성된 두 table의 정보는 다음과 같이 JOIN을 통해 조회한다.

SELECT users.id, users.name, users.email, user_profiles.address, user_profiles.phone
FROM users
INNER JOIN user_profiles ON users.id = user_profiles.user_id;

One To Many Relationship

One To Many relationship은 특정 table의 data가 다른 table의 여러 data row와 relationship을 형성하는 relationship type이다. 예를 들어 ecommerce site에서 user와 order의 관계가 예제가 될 수 있다. 한 명의 user는 상품을 여러번 구매하며 여러 개의 order 내역을 가질 수 있지만 하나의 order 내역이 여러 명의 구입자( user )를 가질 순 없으므로 order 내역은 상품을 구매한 user가 한 명으로 정해져 있다.

다음 예제를 살펴보자.

Users table

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(30),
  email VARCHAR(30),
  PRIMARY KEY(id)
)

Orders table

CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  product_name VARCHAR(30),
  order_date DATE,
  total_price INTEGER,
  user_id INTEGER UNIQUE,
  PRIMARY KEY(id),
  CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(id)
)

One To Many relationship을 형성하기 위해선 many table ( 위의 예제에선 orders )에 main table ( 위의 예제에선 users )의 primary key를 참조하는 foreign key constraint를 통해 one to many relationship을 형성한다. 이제 위와 같이 생성한 table에 data를 추가하는 예제를 살펴보자.

INSERT INTO users (name, email) VALUES ('jake', 'jake@email.com');
/*
id  name  email
1   jake  jake@email.com
*/

INSERT INTO orders (user_id, product_name, order_date, total_price) VALUES (1, 't-shirt 1', '2024-02-01', 1000);
INSERT INTO orders (user_id, product_name, order_date, total_price) VALUES (1, 't-shirt 2', '2024-02-03', 2500);

위의 예제에서 살펴볼 수 있듯이 orders table의 user_id column은 user table id를 reference하는 foreign key constraint이 설정되어 있기에 data를 추가할 때 users table에 존재하는 user id data를 설정하여 추가해야 하며 one to one relationship과는 달리 user id가 중복될 수 있다.

이렇게 one to many relationship이 형성된 두 table의 정보는 다음과 같이 JOIN을 통해 조회한다.

SELECT users.id, users.name, users.email, orders.product_name, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Many To Many Relationship

Many To Many relationship은 a라는 table의 data가 b라는 table의 여러 data row와 relstionship을 형성할 수 있고 b라는 table의 data 역시 a라는 table의 여러 data row와 relationship을 형성할 수 있는 relationship type이다. 예를 들어 온라인 class 서비스에서 student와 course는 서로 many to many relationship을 형성한다.

한 명의 student는 여러 course에 등록할 수 있고 하나의 course 역시 여러 명의 students를 받을 수 있다. Many To Many relationship을 형성하기 위해선 junction table 역할을 하는 table을 생성하고 join table에 두 table의 many to many relationship을 관리하는 data를 저장한다.

다음 예제를 살펴보자.

Student table

CREATE TABLE students (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(30),
  PRIMARY KEY(id)
)

Course table

CREATE TABLE courses (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(30),
  PRIMARY KEY(id)
)

Enrollment table ( join able )

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Many To Many relationship을 형성하기 위해서 junction table 역할을 하는 enrollments table을 생성하고 enrollments table에서 student table의 id를 reference하는 student_id column과 courses table의 id를 reference하는 course_id column을 통해 두 table 사이의 relationship data를 관리한다. 위와 같이 생성한 table에 data를 추가하는 예제를 살펴보자.

INSERT INTO students (name) VALUES ('name a');
INSERT INTO students (name) VALUES ('name b');
/*
id  name
1   name a
2   name b
*/

INSERT INTO courses (course_name) VALUES ('History');
INSERT INTO courses (course_name) VALUES ('English');
/*
id  name
1   History
2   English
*/

INSERT INTO enrollments (student_id, course_id, enrollment_date) 
VALUES (1, 1, '2024-02-01');

INSERT INTO enrollments (student_id, course_id, enrollment_date) 
VALUES (2, 1, '2024-02-07');

INSERT INTO enrollments (student_id, course_id, enrollment_date) 
VALUES (1, 2, '2024-02-05');

INSERT INTO enrollments (student_id, course_id, enrollment_date) 
VALUES (2, 2, '2024-02-07');

위의 예제에서 살펴볼 수 있듯이 name a이라는 student는 history, english 두 class에 등록하고 name b라는 student 역시 history, english 두 class에 등록하고 있다. 반대로 history라는 course 역시 name a, name b 두 명의 student를 포함하고 있고 english course 역시 name a , name b 두 명의 student를 포함하고 있다.

이렇듯 many to many relationship은 두 테이블의 data가 서로 여러 data와 relationship을 맺을 수 있으며 이는 junction table을 생성하여 관리한다 ( 위의 예제에선 enrollments table )

이렇게 many to many relationship이 형성된 두 table의 정보는 다음과 같이 JOIN을 통해 조회한다.

SELECT students.name AS studentname, courses.name AS coursename
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON courses.id = enrollments.course_id;