How to deal with events across more than one day

[dragginthru]

Right this is a fundamental issue for me here and I cant get my head around the problem!!!

The database I am working on is a database which records the training courses (run by the NHS) which staff at GP surgeries in our area have attended and acts as a booking system for them.

So we have the obvious tables - one for courses (including the name, cost, etc) and then one for course dates (linked by courseID) and one called training_record which contains who went to what when.

It seems simple but it has come to my attention that for example, "Course A" may in fact take place over 2 days, which could be a week apart. The surgeries are billed in one invoice, i.e. Course A would be £100 for 2 days.

How would you recommend I have this data? I can see all sorts of confusion arising...

The only way I have thus far thought would be to put each one in like:

course name: cost date
Course A: day 1 £100 - 29/10/2005
Course A: day 2 n/a - 6/10/2005
Course A: day 1 £100 - 01/11/2005
Course A: day 2 n/a - 08/11/2005

However this seems long winded as some courses may be a few days long and this means adding each person to multiple ones!!!!

Any assistance with getting my head around this would be ace!

 

[ScottGem]

Cost and schedule are separate items. The cost is part of the Course record. When you invoice them, you invoice for the course, not the days. If you need a record of the days the course is scheduled create a table linked to the Training record that lists each day (maybe time and place).