Hello experts, I've posted this in under access (which I'm most familiar with) but it's more a design question than specifically for access. I've spent a lot of time thinking around this (mostly quite some time ago) so would welcome some direction, as not a DBA type person!
So what's the best way to design a database for the following;
Goal: To take, and track customer details / payments, and manage event date bookings for (let's say) a hot air ballon company...
So you've got your customers who are making a booking, which may be for themselves or someone else - or even for a number of other participants which may or may not include themselves. This will usually be for a date but they can also get vouchers (which we need to track, of course!) They may also be paying in a variety of ways (deposits / in full etc...) - and other people may get added to a specific date's booking at a later date.
Obviously there is a limit on number of available places for each day.
Also with ballooning the weather may not be suitable on any given day, so they may need to rebook for a later date - possibly a number of times - so it would be good to keep track of the dates for which the participants have previously booked! - this is the main bit I'm trying to work out - where / how to most efficiently keep track of the dates, and the changes to them etc etc....
So we will be looking up by a date to see a list of who is booked on that day.
we will be looking by customers or by participants to see for when they are booked, how they paid, redeemed vouchers, address etc,,,,
we will be looking up by vouchers to see what's outstanding and not redeemed as yet.
Any suggested table's / relationship ideas very welcome (as you can tell from the points!)
e.g. table for CUSTOMERS (that booked a 'party' (maybe only 1 person, even themselves) related to a table for PARTICIPANTS etc...
Many thanks in advance...
