Design a relational database schema for a scheduling platform at Honor, ensuring data consistency and handling concurrent booking requests.
Define entities, relationships, constraints, and a transaction-safe booking approach. Then write a PostgreSQL query that evaluates pending requests against confirmed bookings and higher-priority pending requests.
request_id, client_name, caregiver_name, queue_position, confirmed_conflicts, earlier_pending_conflicts, booking_status.caregiver_name, queue_position, and request_id.| Column | Type | Description |
|---|---|---|
| caregiver_idPK | BIGINT | Unique caregiver identifier |
| caregiver_name | VARCHAR(120) | Caregiver display name |
| timezone | VARCHAR(64) | IANA timezone used for scheduling |
| Column | Type | Description |
|---|---|---|
| client_idPK | BIGINT | Unique client identifier |
| client_name | VARCHAR(120) | Client display name |
| care_plan | VARCHAR(80) | Optional care plan label |
| Column | Type | Description |
|---|---|---|
| request_idPK | BIGINT | Unique booking request identifier |
| client_id | BIGINT | Requested client |
| caregiver_id | BIGINT | Requested caregiver |
| start_at | TIMESTAMPTZ | Requested start timestamp |
| end_at | TIMESTAMPTZ | Requested end timestamp |
| priority | INT | Higher values receive precedence |
| created_at | TIMESTAMPTZ | Request creation time |
| status | VARCHAR(20) | Request lifecycle status |
| Column | Type | Description |
|---|---|---|
| booking_idPK | BIGINT | Unique finalized booking identifier |
| request_id | BIGINT | Originating request |
| client_id | BIGINT | Booked client |
| caregiver_id | BIGINT | Booked caregiver |
| start_at | TIMESTAMPTZ | Booking start timestamp |
| end_at | TIMESTAMPTZ | Booking end timestamp |
| status | VARCHAR(20) | Booking lifecycle status |