[AIoT] 무인 사물함 프로젝트 논리적 DB 설계, ERD 작성

개요

실제 백엔드 로직을 작성하기 앞서 프로젝트 명세에 대해 많은 시간을 할애했다.

주제가 정해진 이후 매일 30분 정도의 스크럼을 통해 각자 파트에서의 기능 구현에 대해 발표했다.

그 중 DB의 경우 모든 파트가 만족할 만한 설계가 완료되어야 하기 때문에 가장 오랜 시간이 걸렸다.

 

로그인 및 회원가입 구현 여부에 대해서도 계속 결정이 번복되었다.

사용자 별 사물함 사용 횟수 및 이용 시간과 그에 따른 혜택 지급을 해주기 위해선 필요했다.

하지만 회원가입을 통해 구현할 수 있는 내용이 그것 뿐이라 결국 스펙에서 제거하기로 결정했다.

 

이 처럼 얽히고 설킨 끝없는 회의를 통해 결국 테이블 구조를 확정짓게 되었다.

동작에 필요한 객체를 각 엔터티 마다 id를 PK로 사용하여 정의하였다.

이후 정규화 과정을 통해 종속성이 없게끔 하여 분리할 수 있을 만큼 분리하였다.

그래서 총 8개의 엔터티가 만들어 졌으며 각 엔터티간 관계를 설정해 주었다.

 

정규화 과정은 진행할 때 마다 항상 어려움이 따르는 것 같다.

특히 현재 엔터티에선 lockers엔터티의 token_id값이 라커 사용 여부에 따라 값이 NULL이 되거나 할당 되는 부분이 과연 올바른 방향성이 맞는지 의심이 들었다.

 

 

논리적 DB

여태 MySQL과 Oracle, 자격증 취득을 위해 Mssql을 다루어 보았다.

PostgreSQL은 이번에 처음 다루게 되었는데 SQL문이 다소 다른부분이 많아 적잖이 당황스러웠다.

예를 들어 show databases; use databases "DB"; show tables;와 같은 쿼리문이 작동하지 않았다.

데이터 타입 또한 AutoIncrement가 적용된 Integer타입은 SERIAL로 나타내는 등 다른 부분이 많았다.

따라서 PostgreSQL에 대해서는 추가적인 공부가 필요해 보였다.

 

우선 AWS EC2에 올리기 전에 로컬 Window환경에서 기능 테스트를 진행할 것이다.

따라서 DB명은 SmartLocker로 예명으로 지어주었고, 테이블 설계 또한 완료하였다.

 

 

ERD

 

 

locker_usage_logs

보관함 사용 이력에 대한 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
log_id	SERIAL	기본키 (Primary Key)	예약의 고유 식별자
locker_id	INTEGER	외래키 (Foreign Key, 참조: Lockers.locker_id)	예약된 락커의 ID
user_id	INTEGER	외래키 (Foreign Key, 참조: Users.user_id)	예약을 한 사용자의 ID
store_time	TIMESTAMP	NOT NULL	락커에 물건이 저장된 시간
store_robot_id	INTEGER	외래키 (Foreign Key, 참조: Robots.robot_id)	물건을 저장한 로봇의 ID
retrieve_time	TIMESTAMP		락커에서 물건을 찾은 시간
retrieve_robot_id	INTEGER	외래키 (Foreign Key, 참조: Robots.robot_id)	물건을 가져온 로봇의 ID

 

 

users

유저의 이용 내역을 조회하고 추가하기 위한 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
user_id	SERIAL	기본키 (Primary Key)	사용자의 고유 식별자
phone_number	TEXT	고유 (Unique), NOT NULL	사용자의 고유 전화번호 (고유값, 필수)

 

 

lockers

각 사물함의 구역에 대한 정보를 확인하기 위한 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
locker_id	SERIAL	기본키 (Primary Key)	락커의 고유 식별자
locker_status_id	INTEGER	외래키 (Foreign Key, 참조: Locker_Status.locker_status_id)	락커의 상태 ID
locker_location_id	INTEGER	외래키 (Foreign Key, 참조: Locker_Locations.location_id)	락커의 위치 ID
token_id	INTEGER	외래키 (Foreign Key, 참조:Access_Tokens.token_id)	배정된 토큰 ID( 라커가 사용중이 아닐 경우 NULL)

 

 

locker_status

라커의 상태 정보를 구분하기 위한 테이블이다.

0. 사용가능 1. 사용중 2. 수리중으로 나타내며 이후 상태 추가 시 3번 - 폐쇄 등으로 유지보수 할 수 있다.

칼럼 이름	데이터 타입	제약 사항	설명
locker_status_id	SERIAL	기본키 (Primary Key)	락커 상태의 고유 식별자 (기본키)
locker_status	TEXT	NOT NULL	락커의 상태 (예: 사용 중, 비어 있음 등

locker_locations(25.01.24 수정)

라커를 섹터를 기준으로 구분하기 위한 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
location_id	SERIAL	기본키 (Primary Key)	위치의 고유 식별자
location_name	TEXT	NOT NULL	위치의 이름
total_lockers	INTEGER	NOT NULL	해당 위치의 전체 락커 수
available_lockers	INTEGER	NOT NULL	해당 위치에서 사용 가능한 락커 수

 

25.01.24

total_lockers, available_lockers 삭제(클라이언트 단에서 처리 진행)

 

 

access_tokens

물품 보관 시 새로이 생성되는 토큰 정보를 보관하기 위한 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
token_id	SERIAL	기본키 (Primary Key)	토큰의 고유 식별자
token_value	INTEGER	NOT NULL	인증 토큰 값

 

 

robots

로봇의 정보와 현황을 나타내기 위한 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
robot_id	SERIAL	기본키 (Primary Key)	로봇의 고유 식별자
robot_name	TEXT	NOT NULL	로봇의 이름
completed_tasks	INTEGER	기본값: 0	완료된 작업 수 (기본값: 0)
last_maintenance	TIMESTAMP	NOT NULL	마지막 유지보수 시간
robot_status_id	INTEGER	외래키 (Foreign Key, 참조: Robot_Status.robot_status_id)	로봇 상태의 ID

robot_status

로봇의 상태를 정의하는 테이블이다.

칼럼 이름	데이터 타입	제약 사항	설명
robot_status_id	SERIAL	기본키 (Primary Key)	로봇 상태의 고유 식별자
robot_status	TEXT	NOT NULL	로봇의 상태 (예: 대기 중, 자동 모드, 수동 모드, 수리 중)