Unique foreign key constraint between three tables

Question

I'd like to track appointment attendances. Visitors can have appointments, or they can just turn up. If they have an appointment, and miss it, in which case I'd like to record a reason for the missed appointment, if known.

These tables seem to cover it:

appointments: id, visitor_id, scheduled_time
attendances: id, appointment_id, arrival_time
missed_appointments: id, appointment_id, reason

How would I add a constraint such that an appointment can't be missed and attended? Is this schema just overcomplicating it?

Answer 1

I would use one table for both completed and missed appointments. Using your notation:

appointments: 
id, 
visitor_id, 
scheduled_time, 
status, 
CHECK(status in('Scheduled', 'Completed', 'Missed')), 
Reason_missed, 
CHECK((status='Missed' and reason_missed is not null) or (status<>'Missed' and reason_missed is null)), 
unique(id, status)

attendances: 
id, 
appointment_id (nullable), 
arrival_time, 
appointment_status,
check((appointment_id is null and appointment_status is null) or (appointment_id is not null and appointment_status in('Scheduled', 'Completed'),
foreign key(appointment_id, appointment_status) references appointments(id, status) on update cascade