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?