[오늘의 학습 일기] 데이터베이스 테이블 설계와 사용자 방문 기록 관리📅 날짜: 2025년 3월 19일

 

 

 

---

📝 오늘의 학습 내용

오늘은 데이터베이스 테이블 설계와 사용자 방문 기록 관리에 대해 공부했습니다. 실무에서 많이 활용되는 데이터 테이블을 설계하는 과정과, 이를 효율적으로 관리하는 방법을 고민하는 시간이었습니다.

---

📌 1. 데이터베이스 테이블 설계

먼저, 여러 기능을 담을 수 있도록 테이블을 설계했습니다. 핵심적으로 다룬 테이블은 아래와 같습니다.

📂 users (사용자 정보 테이블)

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,  -- 사용자 고유 ID (자동 증가)
    username VARCHAR(50) NOT NULL,           -- 사용자 이름
    email VARCHAR(100) NOT NULL UNIQUE,      -- 이메일 (로그인용, 중복 방지)
    password VARCHAR(255) NOT NULL,          -- 암호화된 비밀번호
    sns_provider ENUM('kakao', 'naver', 'none') NULL  -- SNS 로그인 제공자
);

💡 배운 점:

• AUTO_INCREMENT를 사용하여 자동으로 증가하는 user_id를 생성했습니다.
• email 컬럼에 UNIQUE 제약 조건을 추가하여 중복 가입을 방지했습니다.
• SNS 로그인을 고려하여 sns_provider 컬럼을 추가했으며, 기본적으로 none을 허용했습니다.

📂 public_offices (공공기관 정보 테이블)

CREATE TABLE public_offices (
    office_id INT PRIMARY KEY AUTO_INCREMENT,  -- 공공기관 고유 ID
    name VARCHAR(100) NOT NULL,                 -- 기관 이름
    address VARCHAR(255) NOT NULL,              -- 기관 주소
    phone VARCHAR(20) NULL,                     -- 연락처
    opening_hours VARCHAR(100) NULL             -- 운영 시간
);

💡 배운 점:

• 공공기관 정보를 저장하기 위한 테이블을 설계했습니다.
• phone과 opening_hours는 NULL을 허용하여 데이터가 없을 경우에도 삽입이 가능하도록 했습니다.

📂 visit_logs (사용자 방문 기록 테이블)

CREATE TABLE visit_logs (
    visit_id INT PRIMARY KEY AUTO_INCREMENT,  -- 방문 기록 ID
    user_id INT NOT NULL,                     -- 방문한 사용자 ID (users 참조)
    office_id INT NOT NULL,                   -- 방문한 공공기관 ID (public_offices 참조)
    visit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 방문 시간
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (office_id) REFERENCES public_offices(office_id)
);

💡 배운 점:

• visit_time 컬럼에 DEFAULT CURRENT_TIMESTAMP를 설정하여 방문 시점을 자동으로 기록하도록 했습니다.
• FOREIGN KEY를 사용하여 users와 public_offices 테이블을 참조하도록 했습니다.

---

🔍 2. 방문 기록 조회 기능 구현

방문 기록을 조회하는 SQL을 작성했습니다.

SELECT v.visit_id, u.username, p.name AS office_name, v.visit_time 
FROM visit_logs v
JOIN users u ON v.user_id = u.user_id
JOIN public_offices p ON v.office_id = p.office_id
ORDER BY v.visit_time DESC;

💡 배운 점:

• JOIN을 활용하여 visit_logs 테이블과 users, public_offices를 연결했습니다.
• 방문 시간을 기준으로 최신 순으로 정렬했습니다.

---

🛠 3. 개선할 부분

오늘 작업하면서 느낀 몇 가지 아쉬운 점과 개선할 점을 정리해 보았습니다.

🔻 부족했던 점

1. 데이터 무결성 검증 부족
   • email과 password에 대한 입력 검증이 부족했습니다.
   • phone 번호의 형식을 제한하는 규칙이 없었습니다.
2. 로그인 시 비밀번호 암호화 적용 필요
   • 비밀번호를 VARCHAR(255)로 저장했지만, BCRYPT 같은 해시 알고리즘을 적용하지 않았습니다.

✅ 개선할 점

1. CHECK 제약 조건 추가→ 이메일 형식을 검증하는 CHECK 제약 조건 추가
2. ALTER TABLE users ADD CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%');
3. 비밀번호 해싱 적용 (Python 예제)→ Python에서 bcrypt 라이브러리를 활용하여 비밀번호를 해싱
4. import bcrypt password = "mypassword123" hashed_password = bcrypt.hashpw(password.encode(), bcrypt.gensalt()) print(hashed_password)

---

📌 4. 오늘의 마무리

오늘은 데이터베이스 테이블 설계와 사용자 방문 기록 관리에 대해 학습했습니다. 실제 서비스에 적용할 때 고려해야 할 데이터 무결성, 보안 등의 요소를 고민하는 과정이 유익했습니다.

앞으로는 API와 연동하여 이 데이터를 활용하는 기능을 구현할 계획입니다. 오늘 배운 내용을 기반으로 더욱 발전시켜 나가야겠습니다! 🚀

다음 목표: API를 활용한 방문 기록 등록 및 조회 기능 개발

---

📌 오늘 공부한 내용이 도움이 되셨다면 댓글로 의견을 남겨주세요! 😊