schema issues: What's the best route?

[skyhighELS]

Hello everyone, and a great thanks to anyone that can help me with this issue. I will try to be as detailed and precise as I can in order to get the best advice, so please excuse my long explanation. And again thanks for any ideas that will make this task less stressful.

My Situation:

I currently run a small English language school out here in Japan, and i feel i am now in need of a nice database management system to deal with my customers and all of the day-to-day, weekly, monthly, and annual (tax stuff) paperwork for organizing this place much smoother.

I have tried, and tried, and tried, and... you know... I cant get through the relationship process, however I have to admit, after so much research I have learned a great deal about Access, and I see it is a perfect fit for what I need... perhaps.

My School system:

LESSONS & PRICING
I have a small school with various students of all ages. I have different types of lessons (normal lessons) in this school, which I want to account for.
Lessons can run for either 50 min. or 90 min. and they are, of course, different prices depending on the contract the student decides to take.
I have 3 different courses within those 2 time options, for each (GROUP, SEMI-PRIVATE, PRIVATE).

I also have a flat rate course for an English-testing study program which is totally separate from the "normal" lessons. The "normal" lessons are at a
monthly payment plan (although some students pay in advanced), and the testing program is a one time flat fee for the 9-week course.

SCHEDULING


Most lessons are on a regular basis weekly, and if there happens to be a 5-day week on that day, in that particular month, it is a day off (they get 4 days in
a month). Also... as we all are...many people are very busy and I have a very flexible system where the students are able to switch their day , as long as
they go by our "make-up lesson" rules. IT HAPPENS OFTEN... lets say this is one of the main reasons for me making this database!


WHAT I WANT TO DO:

I want a relational database that can take care of the data for scheduling, RESCHEDULING. For instance, if a student decides to reschedule, Id like to hit a button, cancel it and add the make-up day. I also would like the system to automatically count the 4 day, monthly lesson for each student. They all have a specific day for scheduling, but if they change that day for another "normal" lesson day, Id like to be able to do that while saving the records of the last day. Would I need to re-enroll them??

Also, Enrollment details would be great to have just for record.

And also invoices and billing the customers. Its not most necessary, but why not add it as I am getting very busy, and will probably want it eventually. I want to be able to use navigation, which I'm sure is not a problem as far as Access goes for me, to have a system to quickly change lesson times, and when the lesson is cancelled it is automatically taken away from the 4 day lesson time they have in a month.

I just want to know the best schema for this type of relational database style.
what tables would I use and how would I connect them?
All help and advice is greatly appreciated. I can do a lot with access just from what I&ve learned from tutorials, and trial and error, but I just cant figure out the formula for what I want to do. Thank again for your input!

Now I have:



but Im almost 100% sure its way off!!!

[ItsMe]

I would start by creating some query objects to test your relationships. Add tables to the Query Designer and create joins that represent the relationship defined in the Relationships Window (the JOIN should be created automatically when you add a table(s)). Test your relationships/cardinality by typing data into your tables or even directly into your queries. When you start, you might want to change the JOIN type from an INNER to an OUTER by double clicking the line that represents the JOIN. Using LEFT or RIGHT joins might facilitate appending records via a query object.

One thing that stands out to me is the name you are using for tblStudents-Lessons. I would name it tblStudents_Lessons or tblStudentsLessonsJct or anything that does not contain a special character, like -, +, *, etc.

I would question the need for a junction table at this stage. I would approach it from the business logic perspective, first. For instance, you get a phone call and someone wants to enroll. I might have a table that logs events like "Enrollment". This table will likely have attributes like a Date, as well as others. So, it would not be a true junction table.

You might discover that you desire a more complex database that manages customers. So, from the business perspective, you get a phone call and you ask them if they have an active account. Then you can create an account for them if they don't. Using the account, they can manage many students and or lessons.

[ssanfu]

For a start, I would suggest you work through the tutorials here
Roger's Access Library
http://www.rogersaccesslibrary.com/forum/forum46.html

Don't just read through, actually create the dBs.

You should use pencil and paper, cardboard and crayon, a whiteboard, etc. to design your tables. For instance, in the Students table, you have two fields that I wouldn't put in that table: LessonTypeID and LessonFeeID. The students table should describe attributes of a student: name, address, phone, DOB (age can be calculated), etc.
Since LessonTypeID and LessonFeeID do not describe a student, I would move those two fields to maybe tblStudents_Lessons.

In tblLessons there are two fields for textbooks. What happens if you have 3 or 4 text books? I would have another table for textbooks.
Also, I don't see very many date fields. I would think a scheduling/rescheduling dB would be very date sensitive.

Having a normalized table structure (IMO) is one of the most important parts of a dB.
You also need to write down (define) your business rules (before starting to create your dB).