Summary: SQL Server project for a multi-hotel reservation system, login users, price calendars, available rooms, calendar blocks, and support messages.

1. Overview
2. Project Files
3. Prerequisites
4. Data Model - Core Tables
5. Recommended Indexes
6. User-Defined Functions (UDFs)
7. Stored Procedures & Utilities
8. Triggers
9. Usage Examples (queries)
10. Validation & Recommended Tests
11. Deployment / Execution Order
12. Security & Permissions
13. Error Messages & Handling

This project implements a hotel booking system in T-SQL with the following features:

• Independent reservations, prices, support messages, and calendar for each hotel.
• User Registration and Login "Password / Salt"
• Creation of reservations by managing the number of rooms available in relation to reservation overlapping dates and/or blocked dates.
• The same applies to customized daily prices.
• Blocking date ranges via CalendarBlocks per Hotel
• Custom daily prices via CustomPrices per Hotel
• Automatic final price calculation (combining hotel base price and custom daily prices) based on their respective intercessions and unions.
• Reusable functions for overlap checks, user and hotel reports
• Validation of incorrect entry and exit dates.

The goal is to enforce business integrity at the database level and provide reusable query objects for the application layer.

 Project Folder/
 └── SQL/
 ├── Hotel Network Project.sln
 ├── SQL_DOC.md
 └── Schemas/
 ├── DataBase.sql
 ├── Indexes.sql
 ├── Tables.sql
 ├── Functions/
 │ ├── fn_ListBookingsByHotel.sql
 │ ├── fn_ListBookingsByUser.sql
 │ ├── fn_ListRequestedSupportByHotel.sql
 │ ├── fn_Login.sql
 │ └── fn_PriceSetter.sql
 ├── Procedures/
 │ ├── usp_DateTimeRangeChecker.sql
 │ ├── usp_DateRangeChecker.sql
 │ └── usp_CheckBookingsBlocksOverlaps.sql
 ├── Queries/
 │ └── SQLQueryTests.sql
 └── Triggers/
 ├── trg_Check_Booking_Overlaps.sql
 ├── trg_Check_Booking_Overlaps_On_Update.sql
 ├── trg_Check_CalendarBlocks_Overlaps.sql
 └── trg_Check_CustomPrices_Overlaps.sql

• Microsoft SQL Server 2018+ (SQL Server 2019/2022 recommended). CREATE OR ALTER requires a compatible version.
• DDL/DML permissions to create objects in the target schema (typically dbo).
• Optional: tSQLt for unit testing if you want automated tests.

This section describes the primary tables used by the project. Refer to Tables.sql for the exact column definitions and constraints.

• ID INT PK
• Name NVARCHAR(200)
• Location NVARCHAR(255)
• Price DECIMAL(10,2) - base daily price.
• Rooms INT - Total rooms available on overlapping dates.

• ID INT PK (IDENTITY)
• UserID INT (FK → Users.ID)
• HotelID INT (FK → Hotels.ID)
• CheckIn DATETIME2
• CheckOut DATETIME2
• Price DECIMAL(18,2) - Final booking price.
• CreatedAt DATETIME2 (DEFAULT SYSUTCDATETIME())

• ID INT PK
• HotelID INT (FK → Hotels.ID)
• DateIn DATETIME2
• DateOut DATETIME2
• Price DECIMAL(10,2) - Daily price.

• ID INT PK
• HotelID INT (FK → Hotels.ID)
• DateIn DATETIME2
• DateOut DATETIME2

• Users(ID, Username, Email, Name, ...)
• SupportMessages(ID, UserID, HotelID, Message, CreatedDate)

All indexes for triggers, functions, and procedures are in Indexes.sql

Eg:.

CREATE INDEX IX_Bookings_Hotel_DateRange
 ON Bookings (HotelID, CheckIn, CheckOut)
INCLUDE (ID);

Why:

• HotelID is equality-filtered - make it the first key column.
• CheckIn/CheckOut are range filters and help reduce the scanned rows.
• INCLUDE (ID) avoids lookups for COUNT(ID) queries.

Other useful indexes:

• IX_CustomPrices_Hotel_DateRange (HotelID, DateIn, DateOut)
• IX_CalendarBlocks_Hotel_DateRange (HotelID, DateIn, DateOut)
• IX_Users_Email
• IX_Users_Username

Always keep statistics up-to-date (UPDATE STATISTICS) after bulk loads.

Files provided and their purpose:

Main trigger: trg_Check_Booking_Overlaps - INSTEAD OF INSERT on Bookings that enforces:

• CheckIn < CheckOut validation per row.
• CalendarBlocks overlap check (abort if any inserted row intersects a block).
• Capacity check (Rooms) combining existing overlaps and other rows in the same batch.
• Final price computation per inserted row (custom price days + remaining days ×ばつ hotel base price).

Final price calculation (per row):

• For each CustomPrices entry that intersects the booking, compute overlapDays * cp.Price and sum.
• If booking has an explicit Price provided on insert, use it.
• Otherwise: final = sumCustomPrice + (bookingDays - overlapDays) * hotelBasePrice.

Count overlaps for hotel in date range:

DECLARE @Start DATETIME2 = '2025年03月01日 00:00:00', @End DATETIME2 = '2025年03月09日 23:59:59';
SELECT COUNT(ID) AS OverlapCount
 FROM Bookings
WHERE 
 HotelID = 1
AND CheckIn < @End
AND CheckOut > @Start;

Insert booking example (trigger performs validation & price):

INSERT INTO Bookings (CheckIn, CheckOut, UserID, HotelID)
 VALUES ('2025年07月01日', '2025年07月05日', 10, 1);

1. Single-row insert: valid booking inserts successfully.
2. Batch insert exceeding Rooms capacity: whole batch is rejected.
3. Insert where CheckIn >= CheckOut: rejected.
4. Insert intersecting CalendarBlocks: rejected.
5. Booking crossing CustomPrices periods: price is calculated correctly per day.
6. Concurrency test: two concurrent sessions inserting for the same hotel - verify no race conditions.

Test tools and hints:

• Use BEGIN TRAN / ROLLBACK to run non-destructive tests.
• Use SET STATISTICS IO, TIME ON to measure IO and CPU cost.
• Use SQL Server Profiler / Extended Events to detect blocking.

1. DataBase.sql (schemas, server-level setup)
2. Tables.sql (create tables & constraints)
3. Indexes.sql (create indexes - create non-clustered indexes after heavy seed load if needed)
4. fn_*.sql (functions)
5. usp_*.sql (procedures)
6. trg_*.sql (triggers) - create triggers last or disable them during seed load
7. Seed data / migrations

• Create roles like booking_app_read and booking_app_writer with minimal privileges.
• Grant EXECUTE on procedures/functions where appropriate.
• Be cautious with ownership chaining and cross-database access in triggers.

Standard trigger error pattern used in this project:

THROW 51000, 'Friendly error message here', 1;

Common messages:

• The entry date must be earlier than the exit date.
• Maximum number of rooms sold out for this hotel on the specified dates.
• The reservation dates overlap with blocking dates.

• Index: Bookings(HotelID, CheckIn, CheckOut)
• Index: CustomPrices(HotelID, DateIn, DateOut)
• Consider sp_getapplock by HotelID or SERIALIZABLE isolation for critical sections
• Monitor PAGE LATCH and blocking at peak times
• Use filtered indexes for active rows if you have a Cancelled or IsActive flag