多日期选择场景下场馆表的数据库结构设计优化问询

Hey there! Let's tackle this venue availability problem head-on. Your current Hall_tbl has an availibility (quick note: that's likely a typo for availability) field, but as you've noticed, this single field can't track availability across multiple dates—since a venue might be free on Monday but booked solid on Tuesday. Here's how to redesign your tables to fix this:

1. First: Refine the Core Venue Table (Hall_tbl) #

We'll keep all the essential venue details, but remove the single availability field (it's no longer useful for date-specific tracking). Updated table definition:

CREATE TABLE Hall_tbl (
    hall_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    capacity INTEGER NOT NULL
);

I added NOT NULL to the core fields since every venue should have a name, price, and capacity—feel free to tweak this if you need optional fields for your use case.

2. Add a Date-Specific Availability Table #

Create a separate table to track each venue's status per date. This is the key to solving your multi-date availability problem:

CREATE TABLE Hall_Availability_tbl (
    hall_id INTEGER NOT NULL,
    avail_date DATE NOT NULL,
    is_available INTEGER NOT NULL CHECK (is_available IN (0, 1)), -- 0 = unavailable, 1 = available
    PRIMARY KEY (hall_id, avail_date), -- Ensures no duplicate entries for the same venue on the same date
    FOREIGN KEY (hall_id) REFERENCES Hall_tbl(hall_id) ON DELETE CASCADE
);

Why this setup works: #

• Granular control: Each row represents one venue's availability on one specific date. No more ambiguous "available" flags that don't tie to concrete days.
• Data consistency: The composite primary key (hall_id, avail_date) prevents conflicting entries for the same venue-date pair.
• Scalability: If you later need to track availability by time slots (e.g., morning/afternoon blocks), you can add start_time and end_time fields without disrupting your core venue data.

3. Example Queries to Use This System #

• Check if a specific venue is available on a user's chosen date:

SELECT is_available 
FROM Hall_Availability_tbl 
WHERE hall_id = 123 
AND avail_date = '2024-10-05';

• Fetch all available venues on a given date:

SELECT h.hall_id, h.name, h.capacity
FROM Hall_tbl h
JOIN Hall_Availability_tbl ha ON h.hall_id = ha.hall_id
WHERE ha.avail_date = '2024-10-05'
AND ha.is_available = 1;

Bonus: Handling Default Availability #

If you want venues to be "available by default" unless explicitly marked as booked, you have two simple options:

• Pre-populate Hall_Availability_tbl with future dates and is_available = 1 for all venues.
• Write a query that assumes availability if no entry exists for a given date (just be cautious with this approach, as it can introduce edge cases for data consistency).

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