Core tables
resources, availability_slots, reservations
Reservation example
Reservation Service ERD Example
A reservation service ERD example for resources, available time slots, reservations, payments, and cancellations.
Scenario
Reservation Service ERD Example
A service where users reserve resources such as meeting rooms, rooms, classes, or equipment for specific time slots.
Key relation
resources 1:N availability_slots
Main caution
Overlapping time and cancellation history
Requirements
Requirements
- Operators publish resources and available slots.
- Users reserve a slot and may pay for it.
- Cancellation and refund reasons must remain available for support.
Table design
Table design
- id PK
- email UNIQUE
- name
- phone
- id PK
- name
- type
- capacity
- status
- id PK
- resource_id FK
- starts_at
- ends_at
- status
- id PK
- user_id FK
- slot_id FK
- status
- reserved_at
- id PK
- reservation_id FK
- amount
- status
- paid_at
- id PK
- reservation_id FK
- reason
- cancelled_at
Relationships
Relationships
resources 1:N availability_slotsavailability_slots 1:1 reservationsusers 1:N reservationsreservations 1:N reservation_paymentsreservations 1:1 cancellations
Design notes
Design notes
Separate supply from booking
availability_slots are time windows the operator opened. reservations are user claims on those slots.
Prevent duplicate reservations at the database level
A unique constraint on reservations.slot_id can stop two users from booking the same discrete slot.
Store cancellations as history
Deleting a reservation loses support and settlement context. Keep the cancellation reason and timestamp.
Implementation checks
Implementation checks
- Make reservations.slot_id unique when slots are discrete.
- Define a clear timezone policy for starts_at and ends_at.
- Update slot.status when a cancelled slot can be reopened.
More ERD examples
More ERD examples
Use these structures as a starting point, or open a demo canvas before creating an account.