设计一个酒店实体的数据库表时,我们需要考虑以下几个核心组成部分:酒店信息、房间类型、房间库存、订单管理、客户信息等。以下是一个简化的示例,展示了几个关键表的设计:
-
Hotel(酒店表)
- hotel_id (INT, PRIMARY KEY):酒店ID
- name (VARCHAR):酒店名称
- address (VARCHAR):酒店地址
- city (VARCHAR):所在城市
- country (VARCHAR):所在国家
- contact_number (VARCHAR):联系电话
- email (VARCHAR):联系邮箱
- description (TEXT):酒店描述
- rating (FLOAT):评分
- stars (INT):星级
-
RoomType(房间类型表)
- room_type_id (INT, PRIMARY KEY):房间类型ID
- hotel_id (INT, FOREIGN KEY REFERENCES Hotel(hotel_id)):所属酒店ID
- name (VARCHAR):房间类型名称(如单人间、双人间、豪华套房等)
- base_price (DECIMAL):基础价格
- capacity (INT):最大容纳人数
- description (TEXT):房间类型描述
-
Room(房间表)
- room_id (INT, PRIMARY KEY):房间ID
- room_type_id (INT, FOREIGN KEY REFERENCES RoomType(room_type_id)):关联的房间类型ID
- floor (INT):楼层
- room_number (VARCHAR):房间号
- status (ENUM: ‘available’, ‘booked’, ‘maintenance’):房间状态
-
Inventory(房间库存表)
- inventory_id (INT, PRIMARY KEY):
- room_id (INT, FOREIGN KEY REFERENCES Room(room_id)):关联的房间ID
- quantity (INT):当前可预订数量
- is_reserved (BOOLEAN):是否已预留
-
Booking(订单表)
- booking_id (INT, PRIMARY KEY):订单ID
- room_id (INT, FOREIGN KEY REFERENCES Room(room_id)):所订房间ID
- customer_id (INT, FOREIGN KEY REFERENCES Customer(customer_id)):客户ID
- check_in_date (DATE):入住日期
- check_out_date (DATE):退房日期
- total_amount (DECIMAL):订单总金额
- booking_status (ENUM: ‘pending’, ‘confirmed’, ‘cancelled’, ‘checked_in’, ‘checked_out’):订单状态
-
Customer(客户表)
- customer_id (INT, PRIMARY KEY):客户ID
- name (VARCHAR):姓名
- phone (VARCHAR):电话号码
- email (VARCHAR):电子邮件
- address (VARCHAR):地址
这个结构是基础且高度简化的,实际应用中可能还需要添加更多详细信息和关联表,比如支付记录表、评论表等,以满足酒店业务需求的复杂性。
以下是根据上述设计创建SQL表结构的示例:
-- 酒店表(Hotel)
CREATE TABLE Hotel (
hotel_id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100),
contact_number VARCHAR(20),
email VARCHAR(255),
description TEXT,
rating FLOAT,
stars INT
);
-- 房间类型表(RoomType)
CREATE TABLE RoomType (
room_type_id INT PRIMARY KEY,
hotel_id INT,
FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id),
name VARCHAR(100) NOT NULL,
base_price DECIMAL(10, 2),
capacity INT,
description TEXT
);
-- 房间表(Room)
CREATE TABLE Room (
room_id INT PRIMARY KEY,
room_type_id INT,
FOREIGN KEY (room_type_id) REFERENCES RoomType(room_type_id),
floor INT,
room_number VARCHAR(50),
status ENUM('available', 'booked', 'maintenance') NOT NULL
);
-- 房间库存表(Inventory,假设每个房间有一个对应的库存记录)
CREATE TABLE Inventory (
inventory_id INT PRIMARY KEY,
room_id INT,
FOREIGN KEY (room_id) REFERENCES Room(room_id),
quantity INT,
is_reserved BOOLEAN NOT NULL DEFAULT false
);
-- 订单表(Booking)
CREATE TABLE Booking (
booking_id INT PRIMARY KEY,
room_id INT,
customer_id INT,
check_in_date DATE,
check_out_date DATE,
total_amount DECIMAL(10, 2),
booking_status ENUM('pending', 'confirmed', 'cancelled', 'checked_in', 'checked_out') NOT NULL,
FOREIGN KEY (room_id) REFERENCES Room(room_id),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
-- 客户表(Customer)
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
email VARCHAR(255),
address VARCHAR(255)
);
请注意,MySQL中对ENUM类型的定义可能会因版本而异,确保你的数据库版本支持在创建表时直接定义ENUM。此外,为了简化,这里假设每个房间仅对应一个库存记录,实际场景可能需要更复杂的库存管理逻辑。