[AIoT] 무인 사물함 프로젝트 물리적 DB 설계

개요

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

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

 

위 포스팅에서 만든 논리적 DB를 기반으로 물리적 DB를 설계하였다.

엔터티 정보와 관계를 토대로 AI에게 부탁하니 PostgreSQL 환경에서의 SQL문을 모두 작성해 주었다.

이를 토대로 테이블 생성 및 관계 설정, 인덱스 설정까지 한번에 설계가 가능해 졌다. 세상 참 좋아졌다.

 

다만, AI에게 요청 시 FK를 고려하여 테이블 생성에 따른 우선순위를 부여해서 전달해 주어야 한다.

 

 

SQL

-- 1. Users table (참조되지 않는 테이블)
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    phone_number TEXT NOT NULL UNIQUE
);

-- 2. Locker Status table (참조되지 않는 테이블)
CREATE TABLE locker_status (
    locker_status_id SERIAL PRIMARY KEY,
    locker_status TEXT NOT NULL
);

-- 3. Locker Locations table (참조되지 않는 테이블)
CREATE TABLE locker_locations (
    location_id SERIAL PRIMARY KEY,
    location_name TEXT NOT NULL,
    total_lockers INTEGER NOT NULL,
    available_lockers INTEGER NOT NULL
);

-- 4. Access Tokens table (참조되지 않는 테이블)
CREATE TABLE access_tokens (
    token_id SERIAL PRIMARY KEY,
    token_value INTEGER NOT NULL
);

-- 5. Lockers table (users, locker_status, locker_locations, access_tokens 참조)
CREATE TABLE lockers (
    locker_id SERIAL PRIMARY KEY,
    locker_status_id INTEGER NOT NULL,
    locker_location_id INTEGER NOT NULL,
    token_id INTEGER,
    FOREIGN KEY (locker_status_id) REFERENCES locker_status(locker_status_id),
    FOREIGN KEY (locker_location_id) REFERENCES locker_locations(location_id),
    FOREIGN KEY (token_id) REFERENCES access_tokens(token_id)
);

-- 6. Robot Status table (참조되지 않는 테이블)
CREATE TABLE robot_status (
    robot_status_id SERIAL PRIMARY KEY,
    robot_status TEXT NOT NULL
);

-- 7. Robots table (robot_status 참조)
CREATE TABLE robots (
    robot_id SERIAL PRIMARY KEY,
    robot_name TEXT NOT NULL,
    completed_tasks INTEGER DEFAULT 0,
    last_maintenance TIMESTAMP NOT NULL,
    robot_status_id INTEGER NOT NULL,
    FOREIGN KEY (robot_status_id) REFERENCES robot_status(robot_status_id)
);

-- 8. Locker Usage Logs table (lockers, users, robots 참조)
CREATE TABLE locker_usage_logs (
    log_id SERIAL PRIMARY KEY,
    locker_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    store_time TIMESTAMP NOT NULL,
    store_robot_id INTEGER,
    retrieve_time TIMESTAMP,
    retrieve_robot_id INTEGER,
    FOREIGN KEY (locker_id) REFERENCES lockers(locker_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (store_robot_id) REFERENCES robots(robot_id),
    FOREIGN KEY (retrieve_robot_id) REFERENCES robots(robot_id)
);

-- 9. Insert initial values for locker_status
INSERT INTO locker_status (locker_status) VALUES 
    ('Available'),     -- 0
    ('In Use'),        -- 1
    ('Under Maintenance'); -- 2

-- 10. Insert initial values for robot_status
INSERT INTO robot_status (robot_status) VALUES 
    ('Waiting'),
    ('Auto Mode'),
    ('Manual Mode'),
    ('Under Maintenance');

-- 11. Create indexes for frequently accessed columns
CREATE INDEX idx_locker_usage_logs_user_id ON locker_usage_logs(user_id);
CREATE INDEX idx_locker_usage_logs_locker_id ON locker_usage_logs(locker_id);
CREATE INDEX idx_lockers_status ON lockers(locker_status_id);
CREATE INDEX idx_lockers_location ON lockers(locker_location_id);
CREATE INDEX idx_robots_status ON robots(robot_status_id);

-- 12. Add comments on tables
COMMENT ON TABLE locker_usage_logs IS 'Records of locker usage including storage and retrieval times';
COMMENT ON TABLE users IS 'User information table';
COMMENT ON TABLE lockers IS 'Information about individual lockers';
COMMENT ON TABLE locker_status IS 'Possible status values for lockers';
COMMENT ON TABLE locker_locations IS 'Information about locker locations';
COMMENT ON TABLE access_tokens IS 'Access tokens for locker authentication';
COMMENT ON TABLE robots IS 'Information about robots managing the lockers';
COMMENT ON TABLE robot_status IS 'Possible status values for robots';

 

 

결과

총 8개의 테이블이 pgAdmin이 postgres데이터베이스에 생성된 것을 확인할 수 있다.

현재는 루트 권한의 유저로 테스트를 한 것이며 추후 DB서버를 배포할 경우 웹 서버와 사용자 연동이 필요하다.

물론 사용자 마다 권한 부여를 달리 해주어야 한다.