虚构企业会议预订系统数据库设计方案咨询（学校课程任务）

Alright, let's walk through building a practical database design for your meeting room booking system—since you mentioned needing to cover sections 2 and the first half of section 3 from that requirement doc, I'll align this with typical enterprise booking system needs, and break it down step by step so you can map it to your specific requirements easily.

First, we'll start by identifying core business entities, then build out table structures, relationships, and key constraints to support your system's functionality.

1. Core Entity & Table Structure #

1.1 Users & Roles #

Enterprise systems need permission controls, so we'll split user data from role definitions to keep things flexible:

• users (stores basic user info)

  • user_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique user identifier
  • username (VARCHAR(50), UNIQUE NOT NULL): Login account
  • full_name (VARCHAR(100) NOT NULL): User's real name
  • email (VARCHAR(100), UNIQUE NOT NULL): Contact email
  • department_id (INT, FOREIGN KEY): Links to the user's department
  • created_at (DATETIME DEFAULT CURRENT_TIMESTAMP): Account creation time

• roles (defines system permission levels)

  • role_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique role ID
  • role_name (VARCHAR(30), UNIQUE NOT NULL): Role title (e.g., admin, employee, dept_manager)
  • permissions (TEXT): JSON-formatted list of permissions (e.g., ["book_room", "approve_booking", "manage_room"])

• user_roles (many-to-many link between users and roles)

  • user_id (INT, FOREIGN KEY)
  • role_id (INT, FOREIGN KEY)
  • PRIMARY KEY (user_id, role_id)

1.2 Departments #

Organize users by department, which is critical for approval workflows:

• departments
  • department_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique department ID
  • dept_name (VARCHAR(50), UNIQUE NOT NULL): Department name (e.g., "Marketing", "Engineering")
  • manager_id (INT, FOREIGN KEY): Links to the department head's user_id

1.3 Meeting Rooms #

The core resource of your system—include all relevant attributes:

• meeting_rooms
  • room_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique room ID
  • room_name (VARCHAR(50), UNIQUE NOT NULL): Room name (e.g., "3rd Floor Conference Room A")
  • capacity (INT NOT NULL): Maximum number of attendees
  • location (VARCHAR(100) NOT NULL): Physical location
  • status (ENUM('available', 'occupied', 'maintenance') DEFAULT 'available'): Real-time room status
  • description (TEXT): Optional notes (e.g., "Equipped with 4K projector and video conferencing tools")

1.4 Room Equipment #

Track equipment available in each room without duplicating data:

• equipment (master list of all system equipment)

  • equipment_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique equipment ID
  • eq_name (VARCHAR(50) NOT NULL): Equipment name (e.g., "Whiteboard", "Wireless Mic", "Video Conference System")

• room_equipment (many-to-many link between rooms and equipment)

  • room_id (INT, FOREIGN KEY)
  • equipment_id (INT, FOREIGN KEY)
  • PRIMARY KEY (room_id, equipment_id)

1.5 Booking Records #

The heart of your booking workflow—stores all reservation details:

• bookings
  • booking_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique booking ID
  • user_id (INT, FOREIGN KEY): ID of the user who made the booking
  • room_id (INT, FOREIGN KEY): ID of the booked room
  • start_time (DATETIME NOT NULL): Meeting start time
  • end_time (DATETIME NOT NULL): Meeting end time
  • meeting_topic (VARCHAR(200) NOT NULL): Subject of the meeting
  • required_equipment (TEXT): Optional list of extra equipment requested (e.g., "Additional wireless mics")
  • status (ENUM('pending', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending'): Booking status
  • approver_id (INT, FOREIGN KEY): ID of the user who approved/rejected the booking (if required)
  • approval_time (DATETIME): Timestamp of approval/rejection
  • created_at (DATETIME DEFAULT CURRENT_TIMESTAMP): Booking creation time

2. Key Database Constraints & Logic #

• Prevent overlapping bookings: Add a partial unique index to ensure no approved bookings overlap for the same room. For MySQL, this looks like:

  CREATE UNIQUE INDEX idx_room_time ON bookings(room_id, start_time, end_time) WHERE status = 'approved';
  

• Foreign key enforcement: Ensure all linked IDs (like user_id, room_id) exist in their parent tables to avoid invalid data.
• Time validity: Use database triggers or application logic to ensure end_time is always later than start_time.

3. Optional Extensions for Scalability #

• Recurring bookings: Add a recurring_bookings table to store repeat rules (e.g., "Every Monday 10 AM-12 PM") and generate corresponding bookings records automatically.
• Audit logs: Create a booking_logs table to track status changes (e.g., "Booking #123 approved by user #45 at 2024-05-20 14:30") for accountability.
• Index optimization: Add indexes on frequently queried fields like room_id, start_time, user_id, and status to speed up search and filter operations.

内容的提问来源于stack exchange，提问作者Schytheron