Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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
share|improve this answer
1  
+1 , but I guess FK constraint has to be deferred , otherwise I don't see how to perform state transitions... Again, I may be wrong, but I think initially appointment is scheduled for someone, and then it can be either completed or canceled... –  a1ex07 Sep 18 '12 at 22:15
    
@a1ex07 I agree - the FK must be on update cascade. Also I fixed the check constraint on attendances.appointment_status. Thanks! –  A-K Sep 19 '12 at 1:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.