Assassin0UK
Programmer
I'm new to access and databases and I'm trying to design a database where the user will be able to book a customer in for a lesson.
A lesson will be either morning or afternoon, monday to friday.
I originally had a M:N relationship between tblCustomer and tblLesson (as many customers could book lessons and a lesson could be for many customers). I've broken this down to create a intermediate table which I've called tblBookings. Which contains the PK of the two tables.
I want it so a customer can only book on to available lessons. As they only run Mon-Fri I don't want someone booking in on a weekend.
My problem is, and the bit I can't seem to get my head around is how would I hande this. I also need to build up a history of who has booked into which lessons so I can prepare bills etc.
My tblLesson contains the available dates at the moment and my question was going to be how can I create all the entries in this table automatically for the user. So they don't have to manually enter dates into the table for the following weeks.
The customer will be able to book Mon-Fri (any day or all days or combinations), and a slot of either Morning or Afternoon (again either of them or possibly both of them).
Whilst typing this though I've just realised this date field may be better being moved into my intermediate table. My Lesson table instead of holding hundreds of available lessons, would now only have to hold 10 records, one for each lesson. (Why didn't I think of that before)
The bookings intermeadiate table also would have needed its own PK as the two from the other tables wouldn't have worked creating duplicate entries on week 2. Moving the date would also fix this problem and I can also validate the date entry in the intermediate table checking the day of week is not equal to 1 or 7. (Sun or Sat)
This still leaves me with the problem of entering loads of data when the customer books lessons for the following week. Also several customers may be doing this at the same time, although it would probably make more sense to issue them with paper forms to fill in.
A customer could book in for one lesson (or 10 lessons) each week, and also every week if they felt like it. The customer my book on all ten lessons. So someone will have to enter these in one at a time. Is there any way to speed this up.
I hope all that made sense and sorry for thinking whilst typing the question, but it really did help me just sitting back and writing the problem down.
Thanks
A lesson will be either morning or afternoon, monday to friday.
I originally had a M:N relationship between tblCustomer and tblLesson (as many customers could book lessons and a lesson could be for many customers). I've broken this down to create a intermediate table which I've called tblBookings. Which contains the PK of the two tables.
I want it so a customer can only book on to available lessons. As they only run Mon-Fri I don't want someone booking in on a weekend.
My problem is, and the bit I can't seem to get my head around is how would I hande this. I also need to build up a history of who has booked into which lessons so I can prepare bills etc.
My tblLesson contains the available dates at the moment and my question was going to be how can I create all the entries in this table automatically for the user. So they don't have to manually enter dates into the table for the following weeks.
The customer will be able to book Mon-Fri (any day or all days or combinations), and a slot of either Morning or Afternoon (again either of them or possibly both of them).
Whilst typing this though I've just realised this date field may be better being moved into my intermediate table. My Lesson table instead of holding hundreds of available lessons, would now only have to hold 10 records, one for each lesson. (Why didn't I think of that before)
The bookings intermeadiate table also would have needed its own PK as the two from the other tables wouldn't have worked creating duplicate entries on week 2. Moving the date would also fix this problem and I can also validate the date entry in the intermediate table checking the day of week is not equal to 1 or 7. (Sun or Sat)
This still leaves me with the problem of entering loads of data when the customer books lessons for the following week. Also several customers may be doing this at the same time, although it would probably make more sense to issue them with paper forms to fill in.
A customer could book in for one lesson (or 10 lessons) each week, and also every week if they felt like it. The customer my book on all ten lessons. So someone will have to enter these in one at a time. Is there any way to speed this up.
I hope all that made sense and sorry for thinking whilst typing the question, but it really did help me just sitting back and writing the problem down.
Thanks
