Start with tenant and location ownership
A business is the tenant boundary for a single-business or multi-business application. Locations belong to a business and define local operating context, including an IANA time-zone identifier. Put the business key on tenant-owned records or reach it through a relationship that authorization can enforce consistently.
Customers can be global identities or business-scoped contacts, depending on whether cross-business accounts are a real requirement. Avoid merging records across tenants just because email addresses match.
- Business 1 → many Locations
- Business 1 → many Services, Staff, Customers, and Appointments
- Location 1 → many local schedules and appointments
Separate catalog, capability, and capacity
Services describe what can be booked: name, duration, active state, and optional buffer or preparation rules. Staff describe people or bookable resources. A service_staff join records eligibility and can hold service-specific duration or price overrides only if the domain requires them.
Availability is derived from recurring working windows plus time-off and other exceptions. Keep those inputs distinct from appointment rows so a schedule change does not rewrite history.
- Service many ↔ many Staff through ServiceStaff
- Staff 1 → many Availability rules and TimeOff exceptions
- Location can scope service availability, staff assignment, and schedules
Represent the customer request and reservation
An Appointment belongs to a business and usually a location, customer, selected service, and assigned staff/resource. Store start and end as instants; keep the business time zone for interpreting local schedules. If a booking contains several services, use AppointmentService rows and define whether they are sequential, simultaneous, or independently assigned.
Copy a small snapshot of agreed service name, duration, or price when historical records must remain understandable after catalog edits. Decide whether cancelled rows are retained and how rescheduling relates the old and new reservations.
- Customer 1 → many Appointments
- Appointment 1 → one or more AppointmentService rows
- Appointment status transitions should be validated and auditable
Status and notifications are separate concerns
Store the current appointment status on the reservation for efficient reads. If operators need a history, add append-only status events with actor, timestamp, and reason. Notifications should be an outbox or delivery record linked to an event; sending a message is not the same transaction as changing appointment state.
An idempotency key or unique event identifier helps prevent duplicate notifications after retries. Record provider outcomes without copying unnecessary sensitive message content into logs.
Illustrative relationship map
The diagram is a generic teaching aid. It intentionally omits product-specific columns and constraints. Downloadable vector source: /diagrams/appointment-booking-data-model.svg
Constraints to decide before implementation
Use foreign keys for ownership and referential integrity, unique constraints for true business keys, and checks for valid durations and interval ordering. For overlapping appointments, a unique start-time constraint is not enough when service lengths vary; use an overlap-aware database constraint or serialize reservation writes around the constrained resource.
Tenant isolation, row-level security, server authorization, soft deletion, audit retention, and indexes should be designed from actual access patterns. This conceptual reference is not a security review or schema prescription.
- Define whether staff, rooms, or both consume capacity.
- Define whether status values release capacity.
- Test simultaneous confirmation, reschedule races, DST boundaries, and cross-tenant access.