I have a question regarding a somewhat complex table design issue. The situation is:
- There is a table with activities
- There is a table with appointments
- There is a table with invoices
- An appointment is related to an activity
- An activity has a price
- There are 2 types of activities:
* Activities where the customer pays for each appointment
* Activities where the customer pays for the activity only once, no matter how many appointments
- The appointments should have a relation to the invoices, on the invoice you must be able to see how many appointments have taken place
- Appointment activities can be changed, using an update
Currently I have some tables constructed, but it doesn't work properly like described above. The complexity is with the pay-only-once activities. This needs a lot of complex checking in code, to make sure the activity is invoiced only once.
What would you suggest as table design?