STEP 20: プロジェクト③ 予約システム設計【上級】

ホーム > データベース設計・データモデリング > STEP 20

🏨 STEP 20: プロジェクト③ 予約システム設計【上級】

ホテル・レストラン予約システムの完全設計

📋 このステップで学ぶこと
  • 時間軸を考慮した予約システムの設計
  • 空き状況管理テーブルの設計
  • ダブルブッキング防止策
  • キャンセル待ち機能の実装
  • NoSQL併用による検索高速化
  • 完全な設計書の作成

学習時間の目安： 3.5時間　｜　難易度： 上級

📋 1. プロジェクト概要

STEP 19からの続き：最も複雑なシステムへの挑戦

STEP 19ではECサイトのデータベース設計を学びました。最終ステップとなるこのSTEP 20では、さらに高度な予約システムの設計に挑戦します。予約システムは時間軸の管理、ダブルブッキング防止、動的な料金設定など、データベース設計で最も難しい課題を含んでいます。ホテル予約システムを例に、実務で通用する完全な設計スキルを身につけましょう。

システム要件

🎯 ホテル予約システムの機能
  • 部屋管理：部屋タイプ、設備、価格設定
  • 予約管理：宿泊日、部屋、宿泊者情報
  • 空室検索：日付・人数での高速検索
  • 料金計算：曜日別料金、シーズン料金
  • ダブルブッキング防止：同じ部屋の重複予約を防ぐ
  • キャンセル管理：キャンセルポリシー、キャンセル待ち
  • 決済管理：前払い、現地払い、キャンセル料

設計の難しいポイント

⚠️ 予約システム特有の課題
  • 時間軸の管理：チェックイン日・チェックアウト日の扱い
  • 重複予約防止：トランザクションとロック戦略
  • 動的な料金設定：曜日、シーズン、部屋タイプごと
  • 高速な空室検索：大量のデータから即座に検索
  • キャンセル処理：キャンセル料の計算、在庫の戻し

📐 2. エンティティ設計

エンティティ一覧

エンティティ 説明 主な属性
hotels ホテル情報 ID、ホテル名、住所、電話番号
room_types 部屋タイプ ID、タイプ名、定員、基本料金
rooms 実際の部屋 ID、部屋番号、部屋タイプID
users 顧客情報 ID、名前、メール、電話番号
reservations 予約情報 ID、ユーザーID、部屋ID、日付
room_availability 空室状況 部屋ID、日付、在庫数
pricing_rules 料金ルール 部屋タイプ、曜日、料金
payments 決済情報 予約ID、金額、決済方法
cancellations キャンセル履歴 予約ID、理由、キャンセル料
waitlist キャンセル待ち ユーザーID、希望日、部屋タイプ

🗄️ 3. テーブル設計（DDL）

hotelsテーブル

CREATE TABLE hotels ( — 主キー：ホテルの一意識別子 hotel_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — ホテル基本情報 hotel_name VARCHAR(255) NOT NULL, — ホテル名 address VARCHAR(500) NOT NULL, — 住所 phone VARCHAR(20) NOT NULL, — 電話番号 email VARCHAR(255), — 問い合わせメール — チェックイン・チェックアウト時間（TIME型） check_in_time TIME DEFAULT ’15:00:00′, check_out_time TIME DEFAULT ’10:00:00′, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — インデックス：ホテル名検索用 INDEX idx_hotel_name (hotel_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

room_typesテーブル

CREATE TABLE room_types ( — 主キー：部屋タイプの一意識別子 room_type_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — どのホテルの部屋タイプか hotel_id BIGINT UNSIGNED NOT NULL, — 部屋タイプ情報 type_name VARCHAR(100) NOT NULL, — タイプ名（シングル、ツイン、スイートなど） description TEXT, — 部屋の説明 max_occupancy INT UNSIGNED NOT NULL, — 最大定員数 — 料金情報：DECIMAL型で正確に保存 base_price DECIMAL(10, 2) NOT NULL, — 基本料金 — 部屋の詳細情報 size_sqm DECIMAL(5, 1), — 広さ（平米） amenities JSON, — 設備情報（JSON形式で柔軟に保存） created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — 外部キー：ホテル削除時に部屋タイプも削除 FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id) ON DELETE CASCADE, INDEX idx_hotel (hotel_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
📦 JSON型の活用
— amenities（設備）の例 { “wifi”: true, “tv”: true, “minibar”: true, “balcony”: false, “ocean_view”: true }

roomsテーブル

CREATE TABLE rooms ( — 主キー：実際の部屋の一意識別子 room_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — どのホテル・部屋タイプか hotel_id BIGINT UNSIGNED NOT NULL, room_type_id BIGINT UNSIGNED NOT NULL, — 部屋の情報 room_number VARCHAR(20) NOT NULL, — 部屋番号（301、A-201など） floor INT UNSIGNED, — 階数 is_active BOOLEAN DEFAULT TRUE, — 利用可能かどうか created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — 外部キー FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id) ON DELETE CASCADE, FOREIGN KEY (room_type_id) REFERENCES room_types(room_type_id) ON DELETE RESTRICT, — ユニーク制約：同じホテル内で同じ部屋番号は不可 UNIQUE KEY unique_hotel_room (hotel_id, room_number), INDEX idx_hotel_type (hotel_id, room_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

usersテーブル

CREATE TABLE users ( — 主キー：顧客の一意識別子 user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — ログイン情報 email VARCHAR(255) NOT NULL UNIQUE, — メールアドレス（ログインID） password_hash VARCHAR(255) NOT NULL, — パスワードハッシュ — 顧客情報 first_name VARCHAR(50) NOT NULL, — 名 last_name VARCHAR(50) NOT NULL, — 姓 phone VARCHAR(20), — 電話番号 — 住所情報（任意） postal_code VARCHAR(10), address VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — インデックス：メールでのログイン検索用 INDEX idx_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

reservationsテーブル（核心部分）

CREATE TABLE reservations ( — 主キー：予約の一意識別子 reservation_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — 誰がどの部屋を予約したか user_id BIGINT UNSIGNED NOT NULL, room_id BIGINT UNSIGNED NOT NULL, — 宿泊期間：DATE型で日付のみ保存 check_in_date DATE NOT NULL, — チェックイン日 check_out_date DATE NOT NULL, — チェックアウト日 — 予約詳細 num_guests INT UNSIGNED NOT NULL, — 宿泊人数 total_price DECIMAL(10, 2) NOT NULL, — 合計金額 — ステータス管理：ENUM型で固定値のみ許可 reservation_status ENUM(‘pending’, ‘confirmed’, ‘checked_in’, ‘checked_out’, ‘cancelled’) DEFAULT ‘pending’, payment_status ENUM(‘pending’, ‘paid’, ‘refunded’) DEFAULT ‘pending’, — 特別リクエスト（禁煙、アレルギー対応など） special_requests TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, — 外部キー：RESTRICT=予約があるならユーザー・部屋は削除不可 FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT, FOREIGN KEY (room_id) REFERENCES rooms(room_id) ON DELETE RESTRICT, — インデックス：空室検索・予約一覧の高速化 INDEX idx_user (user_id), INDEX idx_room_dates (room_id, check_in_date, check_out_date), INDEX idx_dates (check_in_date, check_out_date), INDEX idx_status (reservation_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
📅 日付の扱い方

check_in_date：宿泊開始日（この日から泊まる）
check_out_date：チェックアウト日（この日の朝に出る）

例：1月1日〜1月3日の予約
→ check_in_date = 2024-01-01
→ check_out_date = 2024-01-03
→ 実際の宿泊日：1月1日、1月2日（2泊）

room_availabilityテーブル（空室管理）

CREATE TABLE room_availability ( — 主キー availability_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — どの部屋のどの日か room_id BIGINT UNSIGNED NOT NULL, availability_date DATE NOT NULL, — 在庫管理 total_capacity INT UNSIGNED DEFAULT 1, — 通常は1部屋=1 reserved_count INT UNSIGNED DEFAULT 0, — 予約済み数 — 空き = total_capacity – reserved_count — メンテナンス等で閉鎖する場合 is_closed BOOLEAN DEFAULT FALSE, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, — 外部キー FOREIGN KEY (room_id) REFERENCES rooms(room_id) ON DELETE CASCADE, — ユニーク制約：同じ部屋・日付は1レコードのみ UNIQUE KEY unique_room_date (room_id, availability_date), INDEX idx_date (availability_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
🔑 room_availabilityの役割
  • 高速検索：日付範囲での空室検索が速い
  • 在庫管理：予約済み数をカウント
  • 柔軟性：メンテナンス日の設定が可能

pricing_rulesテーブル（動的料金設定）

CREATE TABLE pricing_rules ( — 主キー pricing_rule_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — どの部屋タイプに適用するか room_type_id BIGINT UNSIGNED NOT NULL, — ルールの種類（平日・週末・祝日・シーズン） rule_type ENUM(‘weekday’, ‘weekend’, ‘holiday’, ‘season’) NOT NULL, — 料金設定：倍率または固定価格 price_multiplier DECIMAL(4, 2) DEFAULT 1.00, — 基本料金の倍率（1.5=50%増） fixed_price DECIMAL(10, 2), — または固定料金 — 有効期間 valid_from DATE, valid_until DATE, — 曜日指定（0=日曜, 6=土曜） day_of_week INT UNSIGNED, — 有効/無効フラグ is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — 外部キー FOREIGN KEY (room_type_id) REFERENCES room_types(room_type_id) ON DELETE CASCADE, INDEX idx_room_type_dates (room_type_id, valid_from, valid_until) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

paymentsテーブル

CREATE TABLE payments ( — 主キー payment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — どの予約の支払いか reservation_id BIGINT UNSIGNED NOT NULL, — 支払い金額 amount DECIMAL(10, 2) NOT NULL, — 支払い方法・ステータス payment_method ENUM(‘credit_card’, ‘bank_transfer’, ‘cash’, ‘paypal’) NOT NULL, payment_status ENUM(‘pending’, ‘completed’, ‘failed’, ‘refunded’) DEFAULT ‘pending’, — 決済サービスのトランザクションID（照合用） transaction_id VARCHAR(255), — 支払い完了日時 paid_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — 外部キー：支払いがあるなら予約は削除不可 FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) ON DELETE RESTRICT, INDEX idx_reservation (reservation_id), INDEX idx_transaction (transaction_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

cancellationsテーブル

CREATE TABLE cancellations ( — 主キー cancellation_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — どの予約がキャンセルされたか reservation_id BIGINT UNSIGNED NOT NULL, — キャンセル理由（任意） cancellation_reason TEXT, — キャンセル料金と返金額 cancellation_fee DECIMAL(10, 2) DEFAULT 0.00, — キャンセル料 refund_amount DECIMAL(10, 2) DEFAULT 0.00, — 返金額 — キャンセル日時 cancelled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — 外部キー：キャンセル履歴があるなら予約は削除不可 FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) ON DELETE RESTRICT, INDEX idx_reservation (reservation_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

waitlistテーブル（キャンセル待ち）

CREATE TABLE waitlist ( — 主キー waitlist_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, — 誰がどの部屋タイプを希望しているか user_id BIGINT UNSIGNED NOT NULL, room_type_id BIGINT UNSIGNED NOT NULL, — 希望宿泊期間 check_in_date DATE NOT NULL, check_out_date DATE NOT NULL, — 希望人数 num_guests INT UNSIGNED NOT NULL, — ステータス（待機中・通知済み・期限切れ） status ENUM(‘waiting’, ‘notified’, ‘expired’) DEFAULT ‘waiting’, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, — 有効期限（この日時を過ぎたら自動で期限切れ） expires_at TIMESTAMP NOT NULL, — 外部キー FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (room_type_id) REFERENCES room_types(room_type_id) ON DELETE CASCADE, INDEX idx_user (user_id), INDEX idx_room_type_dates (room_type_id, check_in_date, check_out_date), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

🔒 4. ダブルブッキング防止

予約処理のトランザクション

START TRANSACTION; — 1. 予約期間の空室チェック（悲観的ロック） SELECT ra.availability_date, ra.total_capacity, ra.reserved_count, (ra.total_capacity – ra.reserved_count) as available FROM room_availability ra WHERE ra.room_id = 101 AND ra.availability_date >= ‘2024-12-01’ AND ra.availability_date < ‘2024-12-03’ — チェックアウト日は含まない AND ra.is_closed = FALSE FOR UPDATE; — 行ロック — 2. 全日程で空きがあるかチェック — （アプリケーション側で判定） — available > 0 がすべての日付で満たされるか — 3. 空きがない場合はROLLBACK — 空きがある場合は予約を作成 — 4. 予約レコードを作成 INSERT INTO reservations (user_id, room_id, check_in_date, check_out_date, num_guests, total_price) VALUES (123, 101, ‘2024-12-01’, ‘2024-12-03’, 2, 30000.00); SET @reservation_id = LAST_INSERT_ID(); — 5. 空室カウントを更新 UPDATE room_availability SET reserved_count = reserved_count + 1 WHERE room_id = 101 AND availability_date >= ‘2024-12-01’ AND availability_date < ‘2024-12-03’; COMMIT;
🛡️ ダブルブッキング防止の仕組み
  • FOR UPDATE：読み取り時に行ロック
  • トランザクション：複数の更新をアトミックに実行
  • reserved_count：予約済み数をカウント
  • 範囲チェック：全日程で空きがあることを確認

制約による二重チェック

— 予約の重複を検出するトリガー DELIMITER // CREATE TRIGGER check_overlapping_reservations BEFORE INSERT ON reservations FOR EACH ROW BEGIN DECLARE overlap_count INT; SELECT COUNT(*) INTO overlap_count FROM reservations WHERE room_id = NEW.room_id AND reservation_status NOT IN (‘cancelled’) AND ( (NEW.check_in_date >= check_in_date AND NEW.check_in_date < check_out_date) OR (NEW.check_out_date > check_in_date AND NEW.check_out_date <= check_out_date) OR (NEW.check_in_date <= check_in_date AND NEW.check_out_date >= check_out_date) ); IF overlap_count > 0 THEN SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Overlapping reservation detected’; END IF; END // DELIMITER ;

🔍 5. 空室検索の最適化

空室検索クエリ

— 2024年12月1日〜3日で2名宿泊可能な部屋を検索 SELECT rt.type_name, r.room_number, rt.base_price, rt.max_occupancy, COUNT(DISTINCT ra.availability_date) as available_days FROM room_types rt INNER JOIN rooms r ON rt.room_type_id = r.room_type_id INNER JOIN room_availability ra ON r.room_id = ra.room_id WHERE rt.hotel_id = 1 AND rt.max_occupancy >= 2 AND ra.availability_date >= ‘2024-12-01’ AND ra.availability_date < ‘2024-12-03’ AND (ra.total_capacity – ra.reserved_count) > 0 AND ra.is_closed = FALSE AND r.is_active = TRUE GROUP BY rt.type_name, r.room_id, r.room_number, rt.base_price, rt.max_occupancy HAVING available_days = DATEDIFF(‘2024-12-03’, ‘2024-12-01’) — 全日程空いている ORDER BY rt.base_price;
🚀 パフォーマンス最適化
  • インデックス：(room_id, availability_date)の複合インデックス
  • HAVING句：全日程空いている部屋のみ抽出
  • キャッシュ：検索結果を5分間キャッシュ（Redis等）

ElasticsearchやMongoDBの併用

🔥 高速化のためのNoSQL併用

大規模なホテルチェーンの場合、RDBMSだけでは検索が遅くなります。

Elasticsearch活用例：

  • 部屋の属性（設備、眺望等）でのフィルタ検索
  • 地図上での位置検索（Geo検索）
  • ファセット検索（価格帯、評価等）

MongoDB活用例：

  • 空室カレンダーの高速取得
  • 部屋の詳細情報（JSON形式）の保存
  • レビューやQ&Aの保存
// MongoDB – 空室カレンダーの例 { “_id”: ObjectId(“…”), “room_id”: 101, “month”: “2024-12”, “availability”: { “2024-12-01”: { “available”: true, “price”: 15000 }, “2024-12-02”: { “available”: true, “price”: 15000 }, “2024-12-03”: { “available”: false, “price”: 15000 } }, “updated_at”: ISODate(“2024-11-19T10:00:00Z”) }

💰 6. 料金計算ロジック

動的料金計算

— 特定日の料金を取得 SELECT r.room_id, r.room_number, rt.base_price, IFNULL(pr.price_multiplier, 1.00) as multiplier, IFNULL(pr.fixed_price, rt.base_price * IFNULL(pr.price_multiplier, 1.00)) as daily_price FROM rooms r INNER JOIN room_types rt ON r.room_type_id = rt.room_type_id LEFT JOIN pricing_rules pr ON rt.room_type_id = pr.room_type_id AND pr.is_active = TRUE AND ( — 曜日ルール (pr.rule_type = ‘weekday’ AND DAYOFWEEK(‘2024-12-01’) BETWEEN 2 AND 6) OR (pr.rule_type = ‘weekend’ AND DAYOFWEEK(‘2024-12-01’) IN (1, 7)) — シーズンルール OR (pr.rule_type = ‘season’ AND ‘2024-12-01’ BETWEEN pr.valid_from AND pr.valid_until) ) WHERE r.room_id = 101;

🔄 7. キャンセル処理

キャンセル処理のトランザクション

START TRANSACTION; — 1. 予約をキャンセル状態に更新 UPDATE reservations SET reservation_status = ‘cancelled’ WHERE reservation_id = 12345; — 2. キャンセル料を計算（例：前日までは無料、当日は50%） SET @cancellation_fee = ( CASE WHEN DATEDIFF((SELECT check_in_date FROM reservations WHERE reservation_id = 12345), CURDATE()) >= 1 THEN 0.00 ELSE (SELECT total_price FROM reservations WHERE reservation_id = 12345) * 0.5 END ); — 3. キャンセル履歴を記録 INSERT INTO cancellations (reservation_id, cancellation_reason, cancellation_fee, refund_amount) SELECT 12345, ‘お客様都合’, @cancellation_fee, total_price – @cancellation_fee FROM reservations WHERE reservation_id = 12345; — 4. 空室カウントを戻す UPDATE room_availability ra INNER JOIN reservations res ON ra.room_id = res.room_id SET ra.reserved_count = ra.reserved_count – 1 WHERE res.reservation_id = 12345 AND ra.availability_date >= res.check_in_date AND ra.availability_date < res.check_out_date; COMMIT;

📊 8. レポートとダッシュボード

稼働率レポート

— 月別稼働率 SELECT DATE_FORMAT(ra.availability_date, ‘%Y-%m’) as month, COUNT(*) as total_days, SUM(ra.reserved_count) as reserved_days, ROUND(SUM(ra.reserved_count) / COUNT(*) * 100, 2) as occupancy_rate FROM room_availability ra WHERE ra.availability_date >= ‘2024-01-01’ AND ra.availability_date < ‘2025-01-01’ GROUP BY DATE_FORMAT(ra.availability_date, ‘%Y-%m’) ORDER BY month;

売上レポート

— 月別売上 SELECT DATE_FORMAT(r.check_in_date, ‘%Y-%m’) as month, COUNT(*) as total_reservations, SUM(r.total_price) as total_revenue, AVG(r.total_price) as avg_price_per_reservation FROM reservations r WHERE r.reservation_status IN (‘confirmed’, ‘checked_in’, ‘checked_out’) AND r.check_in_date >= ‘2024-01-01’ AND r.check_in_date < ‘2025-01-01’ GROUP BY DATE_FORMAT(r.check_in_date, ‘%Y-%m’) ORDER BY month;

✅ 9. 完全な設計書作成

📄 設計書に含めるべき内容
  1. システム概要：目的、対象ユーザー、主要機能
  2. ER図：エンティティとリレーションシップの全体像
  3. テーブル定義書：各テーブルの詳細仕様
  4. インデックス設計：パフォーマンス最適化
  5. トランザクション設計：重要な処理の流れ
  6. セキュリティ：アクセス制御、データ保護
  7. バックアップ戦略：データ保護方針
  8. スケーラビリティ：将来の拡張性

テーブル定義書の例

【テーブル名】reservations（予約） 【説明】 ホテルの予約情報を管理するテーブル。 顧客が部屋を予約した際の全情報を保持する。 【カラム定義】 ┌────────────────────┬──────────────┬──────┬──────┬─────────────┐ │ カラム名 │ データ型 │ NULL │ キー │ 説明 │ ├────────────────────┼──────────────┼──────┼──────┼─────────────┤ │ reservation_id │ BIGINT │ NO │ PRI │ 予約ID │ │ user_id │ BIGINT │ NO │ FK │ ユーザーID │ │ room_id │ BIGINT │ NO │ FK │ 部屋ID │ │ check_in_date │ DATE │ NO │ IDX │ チェックイン日 │ │ check_out_date │ DATE │ NO │ IDX │ チェックアウト日 │ │ num_guests │ INT │ NO │ │ 宿泊人数 │ │ total_price │ DECIMAL(10,2)│ NO │ │ 合計金額 │ │ reservation_status │ ENUM │ NO │ IDX │ 予約ステータス │ └────────────────────┴──────────────┴──────┴──────┴─────────────┘ 【ビジネスルール】 – check_out_date は check_in_date より後の日付でなければならない – num_guests は部屋タイプの max_occupancy を超えてはならない – キャンセル後は reservation_status が ‘cancelled’ に更新される

📝 10. STEP 20 のまとめ

✅ このステップで学んだこと
  • 時間軸を考慮した予約システムの設計
  • ダブルブッキング防止のためのトランザクション設計
  • 空室管理テーブルによる高速検索
  • 動的料金設定の実装
  • キャンセル処理とキャンセル待ち機能
  • NoSQL併用による検索パフォーマンス向上
  • 完全な設計書の作成方法
💡 重要ポイント
  • 予約システムは時間軸の管理が最も難しい
  • 悲観的ロック（FOR UPDATE）でダブルブッキングを防ぐ
  • room_availabilityテーブルで高速検索を実現
  • 料金計算は複雑なビジネスルールに対応
  • スケーラビリティを考慮してNoSQLも検討
🎉 コース完走おめでとうございます！

データベース設計・データモデリングコースの全20ステップを完了しました！
これで、実務レベルのデータベース設計ができるようになったはずです。

学んだ知識を活かして、実際のプロジェクトに挑戦してみてください。

📝 最終課題

最終課題 総合

レストラン予約システムを設計してください。

要件：

  • 複数のレストラン・店舗を管理
  • テーブル（席）ごとの予約管理
  • 時間帯別の予約（ランチ、ディナー）
  • コース料理の事前予約
  • アレルギー情報の管理
  • 待ち時間の表示

以下を作成してください：

  1. ER図
  2. テーブル定義（DDL）
  3. 予約処理のトランザクション
  4. 空席検索クエリ
【ヒント】

1. 主要エンティティ

  • restaurants（レストラン）
  • tables（テーブル/席）
  • time_slots（時間帯）
  • reservations（予約）
  • courses（コース料理）
  • allergies（アレルギー情報）

2. ホテルとの違い

  • 予約単位が「日」ではなく「時間帯」
  • 1日に複数回転（ランチ、ディナー）
  • 滞在時間の制限（通常2時間）
  • 当日予約・ウォークインが多い

3. 設計のポイント

  • time_slotsで時間帯を管理（11:30-13:30、18:00-20:00等）
  • tablesは人数（2人席、4人席等）で分類
  • reservation_duration（予約時間）を設定
  • allergiesは多対多の関係
追加課題 1 応用

ホテルにレビュー機能を追加してください。

要件：

  • 宿泊後のみレビューを投稿可能
  • 総合評価と項目別評価（清潔さ、接客、立地など）
  • ホテル・部屋タイプごとの平均評価を表示
【解答】
CREATE TABLE reviews ( review_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reservation_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, room_type_id BIGINT UNSIGNED NOT NULL, — 総合評価 overall_rating INT UNSIGNED NOT NULL CHECK (overall_rating BETWEEN 1 AND 5), — 項目別評価 cleanliness_rating INT UNSIGNED CHECK (cleanliness_rating BETWEEN 1 AND 5), service_rating INT UNSIGNED CHECK (service_rating BETWEEN 1 AND 5), location_rating INT UNSIGNED CHECK (location_rating BETWEEN 1 AND 5), facility_rating INT UNSIGNED CHECK (facility_rating BETWEEN 1 AND 5), value_rating INT UNSIGNED CHECK (value_rating BETWEEN 1 AND 5), title VARCHAR(200), comment TEXT, is_verified BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) ON DELETE RESTRICT, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id) ON DELETE CASCADE, FOREIGN KEY (room_type_id) REFERENCES room_types(room_type_id) ON DELETE CASCADE, UNIQUE KEY unique_reservation_review (reservation_id), INDEX idx_hotel (hotel_id), INDEX idx_room_type (room_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; — ホテルの平均評価を取得 SELECT h.hotel_id, h.hotel_name, COUNT(*) as review_count, ROUND(AVG(r.overall_rating), 2) as avg_overall, ROUND(AVG(r.cleanliness_rating), 2) as avg_cleanliness, ROUND(AVG(r.service_rating), 2) as avg_service, ROUND(AVG(r.location_rating), 2) as avg_location FROM hotels h LEFT JOIN reviews r ON h.hotel_id = r.hotel_id GROUP BY h.hotel_id, h.hotel_name;
追加課題 2 発展

会員ランク制度を追加してください。

要件：

  • 宿泊回数に応じてランクが上がる（ブロンズ→シルバー→ゴールド→プラチナ）
  • ランクに応じた割引率を適用
  • ランクアップ時に通知
【解答】
— 会員ランクマスタ CREATE TABLE membership_ranks ( rank_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rank_name VARCHAR(50) NOT NULL, min_stays INT UNSIGNED NOT NULL, — 必要宿泊回数 discount_rate DECIMAL(4, 2) DEFAULT 0.00, — 割引率 priority_booking BOOLEAN DEFAULT FALSE, — 優先予約 late_checkout BOOLEAN DEFAULT FALSE, — レイトチェックアウト created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; — 初期データ INSERT INTO membership_ranks (rank_name, min_stays, discount_rate, priority_booking, late_checkout) VALUES (‘ブロンズ’, 0, 0.00, FALSE, FALSE), (‘シルバー’, 5, 0.05, FALSE, FALSE), (‘ゴールド’, 15, 0.10, TRUE, FALSE), (‘プラチナ’, 30, 0.15, TRUE, TRUE); — ユーザーの会員情報 CREATE TABLE user_memberships ( user_id BIGINT UNSIGNED PRIMARY KEY, rank_id INT UNSIGNED NOT NULL DEFAULT 1, total_stays INT UNSIGNED DEFAULT 0, total_spent DECIMAL(12, 2) DEFAULT 0.00, member_since DATE NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (rank_id) REFERENCES membership_ranks(rank_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; — チェックアウト時にランクを更新するトリガー DELIMITER // CREATE TRIGGER after_checkout_update_rank AFTER UPDATE ON reservations FOR EACH ROW BEGIN DECLARE current_stays INT; DECLARE new_rank_id INT; IF NEW.reservation_status = ‘checked_out’ AND OLD.reservation_status != ‘checked_out’ THEN — 宿泊回数を更新 UPDATE user_memberships SET total_stays = total_stays + 1, total_spent = total_spent + NEW.total_price WHERE user_id = NEW.user_id; — 現在の宿泊回数を取得 SELECT total_stays INTO current_stays FROM user_memberships WHERE user_id = NEW.user_id; — 新しいランクを決定 SELECT rank_id INTO new_rank_id FROM membership_ranks WHERE min_stays <= current_stays ORDER BY min_stays DESC LIMIT 1; — ランクを更新 UPDATE user_memberships SET rank_id = new_rank_id WHERE user_id = NEW.user_id; END IF; END // DELIMITER ;

❓ よくある質問

Q1: 予約システムで最も重要なポイントは何ですか？

ダブルブッキングを絶対に防ぐことです。同じ部屋や席を2組に予約してしまうと、お客様に大きな迷惑をかけます。そのため、トランザクションと悲観的ロック（FOR UPDATE）を使って、確実に防ぐ設計が必須です。また、アプリケーション側のチェックだけでなく、データベース側でもトリガーや制約で二重チェックすることをおすすめします。

Q2: room_availabilityテーブルは必須ですか？reservationsテーブルだけではダメですか？

reservationsテーブルだけでも動作しますが、検索が非常に遅くなります。空室検索のたびにreservationsテーブルの全レコードをスキャンする必要があり、データ量が増えるとパフォーマンスが劣化します。room_availabilityテーブルを使えば、日付範囲での検索が高速になります。初期データ投入の手間はありますが、運用時のパフォーマンスを考えると必須といえます。

Q3: NoSQLを併用する基準は何ですか？

検索パフォーマンスが重要な場合です。例えば、複雑な条件での検索（設備、位置、価格帯等）や、大量のデータから高速に検索する必要がある場合、ElasticsearchやMongoDBの併用を検討します。ただし、マスターデータはRDBMSに保存し、検索用インデックスとしてNoSQLを使うのが一般的です。データの二重管理になるため、同期の仕組みも必要になります。

Q4: キャンセル料の計算はどこで行うべきですか？

アプリケーション層で計算するのが一般的です。キャンセルポリシーはビジネスルールなので、頻繁に変更される可能性があります。データベースのストアドプロシージャに書くと変更が大変なので、アプリケーション側で計算し、結果だけをDBに保存します。また、キャンセルポリシー自体をテーブル化しておくと、管理画面から変更できるようになります。

Q5: 連泊割引はどう実装すればよいですか？

pricing_rulesテーブルに連泊ルールを追加します。例えば、rule_type=’consecutive_nights’として、min_nights（最低泊数）とprice_multiplier（割引率）を設定します。料金計算時に宿泊日数をカウントし、該当するルールがあれば割引を適用します。複数の割引が適用可能な場合のルール（併用可否、最大割引額など）も事前に決めておく必要があります。

Q6: 予約システムのスケーラビリティを向上させるには？

読み取りと書き込みを分離することが効果的です。空室検索（読み取り）はレプリカDBやNoSQL（Elasticsearch等）に任せ、予約処理（書き込み）はマスターDBで行います。また、空室カレンダーをRedisにキャッシュしておき、5分ごとに更新する方法も有効です。人気のホテルや日付は特にアクセスが集中するため、キャッシュ戦略が重要になります。

← 前のステップ
STEP 19: プロジェクト② EC設計 コーストップに戻る
全20ステップ完了！
📝

学習メモ

データベース設計・データモデリング - Step 20

💾 自動保存 · 0文字
📋 過去のメモ一覧 ▼
#artnasekai #学習メモ
LINE
ホーム
データベース設計・データモデリング