[메타버스 게임] 캐쥬얼 배틀로얄 프로젝트 ERD 제작, 물리 DB 구현

개요

본격적인 코드 작성에 앞서 데이터를 수집 및 검증해야할 것이 무엇이 있는지 생각해 보았다. 처음엔 CRUD 트랜잭션 처리를 통해 DB검증부터 로그 수집까지 모든걸 진행할 생각이었다. 하지만 게임 내 발생하는 이벤트 동기화는 Unity Mirror을 통해 진행할 것이므로 이는 좋은 선택이 아니라는 것을 깨달았다.

 

따라서 게임 내부에서 발생한 로그 관련 데이터는 모두 별도의 로그 서버에서 처리하기로 했고, 나는 회원가입, 로그인 등과 같이 게임의 시작부터 로비에 진입하여 방 생성, 참가, 퇴장 및 게임 시작, 종료등과 같은 이벤트 발생 시 DB검증을 통한 클라이언트 요청 동기화를 담당하기로 했다.

 

관련하여 초기 엔터티의 개수는 12개였고 각 테이블간 관계도 많이 설정되었었다, 하지만 실제 검증이 필요한 로직만 남기니 테이블의 개수가 5개로 확 줄어버렸다. 여기서도 사실상 1개의 테이블은 유지보수 면에서 남긴 것 이므로 현재 활발하게 CRUD가 이뤄지는 테이블은 총 4개이다. 관련하여 ERD는 다음과 같다.

 

 

ERD

초기 ERD(3/6)

 

1차 수정 ERD(3/19)

 

2차 수정 ERD(3/21)

3차 수정 ERD(3/27)

 

 

테이블 구조

users

컬럼명	데이터 타입	설명	제약 조건
user_id	SERIAL	사용자 고유 식별자	PRIMARY KEY
user_name	VARCHAR(50)	사용자 이름	NOT NULL, UNIQUE
nick_name	VARCHAR(16)	사용자 닉네임	DEFAULT 랜덤 생성값
password_hash	VARCHAR(255)	비밀번호 해시	NOT NULL
created_at	TIMESTAMP WITH TIME ZONE	계정 생성 시간	DEFAULT CURRENT_TIMESTAMP
last_login	TIMESTAMP WITH TIME ZONE	마지막 로그인 시간	DEFAULT CURRENT_TIMESTAMP

 

maps

컬럼명	데이터 타입	설명	제약 조건
map_id	SERIAL	맵 고유 식별자	PRIMARY KEY
map_name	VARCHAR(50)	맵 이름	NOT NULL, UNIQUE

 

rooms

컬럼명	데이터 타입	설명	제약 조건
room_id	SERIAL	방 고유 식별자	PRIMARY KEY
room_name	VARCHAR(100)	방 이름	NOT NULL
host_id	INTEGER	방장 ID	NOT NULL, REFERENCES users(user_id)
ip_address	VARCHAR(45)	방 IP 주소	-
port	INTEGER	방 포트 번호	-
max_players	INTEGER	최대 플레이어 수	NOT NULL, DEFAULT 8
status	VARCHAR(20)	방 상태	NOT NULL, DEFAULT 'TERMINATED', CHECK ('WAITING', 'GAME_IN_PROGRESS', 'TERMINATED')
created_at	TIMESTAMP WITH TIME ZONE	방 생성 시간	DEFAULT CURRENT_TIMESTAMP

 

room_users

컬럼명	데이터 타입	설명	제약 조건
room_id	SERIAL	방 ID	NOT NULL, REFERENCES rooms(room_id), PRIMARY KEY(room_id, user_id)
user_id	INTEGER	사용자 ID	NOT NULL, REFERENCES users(user_id), PRIMARY KEY(room_id, user_id)
joined_at	TIMESTAMP WITH TIME ZONE	방 참가 시간	DEFAULT CURRENT_TIMESTAMP

 

games

컬럼명	데이터 타입	설명	제약 조건
game_id	SERIAL	게임 고유 식별자	PRIMARY KEY
room_id	SERIAL	연결된 방 ID	NOT NULL, REFERENCES rooms(room_id)
map_id	INTEGER	선택된 맵 ID	NOT NULL, REFERENCES maps(map_id)
status	VARCHAR(20)	게임 상태	NOT NULL, DEFAULT 'IN_PROGRESS', CHECK ('IN_PROGRESS', 'COMPLETED')
started_at	TIMESTAMP WITH TIME ZONE	게임 시작 시간	DEFAULT CURRENT_TIMESTAMP
completed_at	TIMESTAMP WITH TIME ZONE	게임 종료 시간	-

 

Index

테이블	인덱스 이름	인덱스 칼럼
users	idx_users_username	user_name
users	idx_users_nickname	nick_name
rooms	idx_rooms_status	status
rooms	idx_rooms_created_at	created_at
room_users	idx_room_users_user_id	user_id
games	idx_games_room_id	room_id
games	idx_games_status	status

 

 

물리 DB 구현

위 테이블 정보를 기반으로 물리 DB를 구현하였다. 개발 초기 단계에서는 DB초기화와 더미데이터 추가 관련 SQL문이 포함되는것이 좋은 것 같다.

 

-- PostgreSQL 유저 생성 (존재하지 않는 경우에만)
DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE rolname = 'admin') THEN
      CREATE USER admin WITH PASSWORD 'admin' SUPERUSER;
   END IF;
END
$$;

-- 아래 부분은 PostgreSQL 클라이언트에서 DB로 이동 후 명령으로 연결 변경 필요

-- 테이블 삭제 (역순으로 삭제하여 참조 무결성 유지)
DROP TABLE IF EXISTS games CASCADE;
DROP TABLE IF EXISTS room_users CASCADE;
DROP TABLE IF EXISTS rooms CASCADE;
DROP TABLE IF EXISTS maps CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- 사용자 테이블
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL UNIQUE,
    nick_name VARCHAR(16) DEFAULT 'user_' || substring(md5(random()::text), 1, 10),
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 클래스 테이블
CREATE TABLE maps (
    map_id SERIAL PRIMARY KEY,
    map_name VARCHAR(50) NOT NULL UNIQUE
);

-- 방 테이블 (컨테이너 기반)
CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,   -- 컨테이너 ID 또는 커스텀 인덱스 
    room_name VARCHAR(100) NOT NULL,
    host_id INTEGER NOT NULL REFERENCES users(user_id),
    ip_address VARCHAR(45),            -- IPv4/IPv6 주소
    port INTEGER,                      -- 포트 번호
    max_players INTEGER NOT NULL DEFAULT 8,
    status VARCHAR(20) NOT NULL DEFAULT 'TERMINATED',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CHECK (status IN ('WAITING', 'GAME_IN_PROGRESS', 'TERMINATED'))
);

-- 방 참가자 테이블 (현재 상태만 관리)
CREATE TABLE room_users (
    room_id SERIAL NOT NULL REFERENCES rooms(room_id),
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (room_id, user_id)
);

-- 게임 테이블 (게임 결과 포함)
CREATE TABLE games (
    game_id SERIAL PRIMARY KEY,  -- 컨테이너 ID와 연계 또는 고유 식별자
    room_id SERIAL NOT NULL REFERENCES rooms(room_id),
		map_id INTEGER NOT NULL REFERENCES maps(map_id),
    status VARCHAR(20) NOT NULL DEFAULT 'IN_PROGRESS',
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE,
    CHECK (status IN ('IN_PROGRESS', 'COMPLETED'))
);

-- 인덱스 생성 (자주 조회되는 필드)
CREATE INDEX idx_users_username ON users(user_name);
CREATE INDEX idx_users_nickname ON users(nick_name);
CREATE INDEX idx_rooms_status ON rooms(status);
CREATE INDEX idx_rooms_created_at ON rooms(created_at); -- 생성 시간 기준 정렬
CREATE INDEX idx_room_users_user_id ON room_users(user_id);
CREATE INDEX idx_games_room_id ON games(room_id);
CREATE INDEX idx_games_status ON games(status);

-- 생성된 테이블에 대한 모든 권한 부여
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;

-- 향후 생성될 테이블과 시퀀스에 대한 권한 설정 (옵션)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO admin;

INSERT INTO users (user_id, user_name, nick_name, password_hash)
VALUES (0, 'Mirror', 'Manager', 'Mirror');

INSERT INTO maps (map_name) VALUES ('SSAFY');
INSERT INTO maps (map_name) VALUES ('Playground');

DO $$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 0..19 LOOP
        INSERT INTO rooms (room_name, host_id, ip_address, port)
        VALUES ('room' || i, 0, '127.0.0.1', 40000 + i);
    END LOOP;
END $$;