4 replies to this topic

[mrdee]

I have been looking around if I can find a template or other model for a database (table), I tried Microsoft Office to see if there was a suitable Access template, but I didn't really see what I was after.

Are there any places on the Internet where you can find database templates?
Or, if anyone can give me some advice,what I want to achieve is the following:

I have a database table (have both an Access and MySQL one) with the personal details of my private students in it.
It obviously contains an auto-incrementing ID field as primary key.

What I would like now is another table to keep track of their lesson day(s) and payments made or owed.
The reason why I need their lesson day and attendance included is because, if they do not attend a lesson without giving proper notice, they get charged as if they had attended their lesson.

I am not really sure how to set it up, or what fields to use, but basically, I need to be able to keep track of their payments (some pay every session, some pay monthly) and be able to send them a reminder (email, letter, or both) if there are any outstanding balances.
So, apart from the fact that the frequency of payment can differ for some people (session-per-session or monthly), there are also different charges, depending on whether they do 1 hour or 30 minute sessions.

After that (but that is the least of my worries at the moment,I first want to get this payment tracking table out of the way), I would like to make a relationship with another table which is a basic Income/Expenditure table, but as I said, no rush for that, it also seems much simpler to do than what I want to achieve now.

So, if anyone can give me some tips how I can go about such a table, eg. what fields to use etc. (but please, keep it simple, as I am not an experienced database developer, I want to do this for my own use, but also to learn more about developing databases at the same time).

So, any advice on how to lay out my table and which fields to use will be welcomed with open arms.
(Obviously, any referral to a site or a template will be gratefully accepted too).

Edited by mrdee, 07 March 2011 - 12:14 PM.

[BCD]

Thanks for an interesting post after a long time.

First thing I would like to ask you, whether you would like to work with the data at the database level or create a front end for it using php etc? Although MySQL data can be comfortably browsed using "MySQL Query Browser" or other similar tools. But since you mention about email reminders and things like that, I guess you are looking at a more friendlier way to consume the data.

Coming to the part of creating the layout of the database (I only talk about MySQL in this post). I know its the most interesting and challenging part to create tables and set up the fields and their relations so that their is no redundancy. There are tools like MySQL Workbench which would free up the brain of having to handle a lot of fields and their relations while creating the database.

It would be easier for me to grasp the layout, if you would have mentioned all the fields you came up with till now.

Except the primary key (which would act as a unique id for a particular student), let us keep the first table (containing student info) aside for a while (although other details like 'joining date' might affect data on other tables, for example you would want to remove a student who has taken more than specified time to complete the course, etc).

Ah, well before I can think further I need to know some details like:
1) Is the fee for a particular session (say 30 min) the same for *any student?

If the fee for a particular session is same, then we could come up with a separate table containing charges for some particular session. Thus the primary key of this table can be related to next table.

The third table may be used for logging purpose. This table might contain these fields:
1) The student primary key (related from first table)
2) Date field (the day this particular student takes session)
3) Time field (if its possible to take multiple sessions per day then this field is important).
4) Session type (related from 2nd table, which says which type of session (30 min/ 1 hour etc) has the student taken for this particular session)
5) Payment field (Store whatever the student pays for this particular session, enter 0 if no amount paid. This data is worked upon in conjunction with all the dates logged in for this particular student and the session type costs).
6) Attendance field (stores true or false registering the attendance of the student).

This is very rough idea if what could be built upon for the database. Here is a sample Database model to get you started with brainstorming (need to download MySQL Workbench to open and edit this): http://www.mediafire...dwhqgdfxcdxttdj

http://imgur.com/NUNa9

May be you can post back the file with your updates so we can have a look at. Just show us the roughest idea and fields which you can think of no matter how unorganized it may be to show us the base idea of your system. Later, I am sure there are some db geeks here who are pros in normalizing db's. If you would like I would be glad in helping you with this and some PHP things which can be coded up for this system.

Edited by BCD, 08 March 2011 - 08:46 PM.

[mrdee]

BCD,
thank you ever so much for such a great and detailed input you are sending.

It is always good to see there are still people out there willing to help others.

Now, first of all:

About the front-end, I think of doing that with PHPRunner once the database has been made, of course, if you have any better suggestions, feel free to enlighten me.

The fee is normally the same for students, there is a 30 minute and a 1 hour fee, one exception, when people enrol 2 or more siblings (or 2 or more family members like father and son etc.), they get a discount, but that is a very rare occurrence.

About the fields:
The student table is pretty standard:
Name, First name, DOB, Address, Town, County, Post Code, Telephone, Mobile, Email, Lesson taken, Picture, and finally a "File" field where I store a Word file with their notes.

Like you suggest, I had indeed thought about a date field in the next table (the date they take a lesson, and most people just have their 1 session a week), a field for the charge (0 if not paid) and a session type field (30 mins or 1 hour), was thinking about a field to distinct between the people who pay every session (the majority) and the ones who pay monthly, but wasn't 100% sure how to go about that. A true/false field for attendance would also be appropriate.

There might also be an option for people booking slots, so I can immediately tell them whether or not a certain slot is available.


An then I thought of opting for a final table, a sort of Income/Expenditure table, a sort of limited book keeping (with room not to send certain figures to the tax man, of course ).

It might all seem very desorganised to you (because it probably is), but it would serve 2 purposes: a) it would help me improve my database development skills (which are quite basic for multi-table relational databases at this stage), and b)it would also be my 1st major project towards the automation of business.

That is why I am very grateful you want to put up with my stupid questions and for your patience and willingness to help.

Just keep your questions coming.

Thank you ever so much.

Edited by mrdee, 09 March 2011 - 12:35 AM.

[mrdee]

An update to the situation:

I have now made a table called "lessons" and linked it to the "Student" table.
The Primary Key in Students is ID (an auto-incrementing number), and the foreign key in Lessons is ServiceID (a non-auto-incrementing number).

So far it works, in that, when a student is selected, there is a link there to the Lessons table, and it shows when clicked.
(Remember, I make the pages using PHPRunner).

However, I am a bit stuck now in what to add to reach my goal:

The table contains: Lesson (a date field where the date of the attended/to be attended lesson is stored), Cost (numeric field,the charge per lesson), Attended (a Yes/No field showing whther or not the student attended the lesson on that day) and Sufficient Notice (another yes/no field to show, if the student did not attend, whether enough notice was given for not attending, and then to take the appropriate action, ie. charge the student or not).

I am now a bit stuck on how to set up other fields, such as differentiating between the students who opted to pay every lesson, and the ones who chose for monthly payment, and also how to set up fields to keep track of (in case of monthly payment) when the due date is, and (in case of both options), whether they have paid or not.

So, if any further useful advice could be given, I would be very grateful.

[mrdee]

It seems like I was abandoned abit here, just as I was beginning to make some progress.

Just to recap (and to give the latest updates in the mean time):

I have managed to set something up in both MySQL and Microsoft Access, thanks to the help of PHPRunner, the MySQL database (with 2 tables and a one-to-one relationship so far), is beginning to take shape.

i have managed to link the students table to a table called "Lessons", and I can nicely switch between both tables, add, edit, remove data in both, also, when data in one table, which have an impact on data in the other table get altered, the alterations seem to take place properly in both tables.

So far so good in MySQL, but I am obviously kind of stuck now on how to take things further and make a table to control payments, so any further advice would be welcomed with open arms.

As far as Microsoft Access goes, I do have some problems there, but first of all, people might be wondering/asking "why make a database system in both MySQL and Microsoft Access"?

the answer is simply: for practice purposes, and then, I would also use the one which seems to be structured best and which seems to work the best out of the two.

But, as I said, I want to learn as much as I can about database development, so it seems a good idea to learn about different systems/platforms.

Anyway, back to the point now:
In Access I created a table with student information, simple enough, and then I created, just as I had done in the MySQL database, a lessons table, which so far contains 3 fields: a student ID (the foreign key), a lesson date field (a date field) and a lesson time field (time field), so in field 2 I plan the dates of the lessons, and in field 3 the time of every lesson (don't know whether that is OK or clumsy).

However, when I set up the one-to-one relationship, Access seemed seemed to accept it, gave me no errors (I set up the relationship from the Primary Key in students (Student ID) to the Foreign Key in lessons (ID), but when I look at the student table, I can see no reference anywhere to the lessons table, or the other way round, or should I set this up manually in the form view?

In the MySQL database, I can effortlessly switch between the two tables, but not in Microsoft Access.
Not sure where I am going wrong here.

All help will be gratefully accepted.