Dancing school database

[George]

Dear friends,

I am in the process of developing a database for a dancing school, in which
many students will be recorded and each student will have to pay for each
month an amount. ProblemA – Should I allow each student to be recorded every
year (with his new personal data, including new class) or shall I link each
student with a second table (year, class)? Also what about students attending
many classes?

I am thinking of the following starting structure:

Tbl_Students
1.StudentID – Primary key
2.Student name, surname etc
3. RegistrationDate
4. ClassID – drop down from another table
5. etc, etc.

In case of having a student only recorded once here I will create a table –
tbl_StucentsClass (StudentID, ClassID, Year etc)

Tbl_Invoices or Payments
1. ReceiptID – Number Long Integer (using a form a will get the max+1)
2. DatePaid etc
3. Month (month for which payment is made)
4. Year (year of month for which payment is made)
5. Amount
6. etc.

My problem is that I will need to know who has paid and who has not for each
month.

For example: StudentA who has registered in January, 2008 has paid for
January, February and March , but now is April, lets say – so the program
must tell me that he hasn’t pay yet for April

StudentB who has registered in February, 2008 has paid for January, February
(now is April) – so the program must tell me that he hasn’t pay for March and
April). Also, since he has registered in February, January must not be
considered as unpaid.

Thanking you in advance,

GeorgeCY

 

[Fred]

Your first step is to decide what you want the databases to record/track.

I think that you have done and explained this thoroughly to the point of
aying that you want to track students, invoices and payments. But you hint
at tracking classes or attendance of students in classes without really
explaining that.

If we take a few guess in that area, here are a few thoughts.

Table of students. Like you describe, except take out the stuff about
classes.

Table of classes (just to populate) a dropdown list) Unless the list and
the words in it are very short, add a PK ClassIDNumber field. .

Table of instances of students being registered in a class. Fields include
the StudentIDNumber, the ClasIDNumber, plus whatever field are useful to you
(e.g. DataStarted

Hopefully that should give you a foundation to do everything that you are
looking to do.

Fred



An invoice/payment table somewhat as you describe. With a record for each
instance of doing a billing (eg. monthly billing) plus each instance of a
payment. The the money opposite signs (positive / negative)

 

[George]

Dear Fred, Thanks a lot for your quick response,

I have downloaded the Classroom management template and I got some more ideas.

So, I beleive I will build a table for classes, table for current classes
(each year), table for students and table for CurrentClasses/Students.

As you have mentioned: "An invoice/payment table somewhat as you describe.
With a record for each instance of doing a billing (eg. monthly billing) plus
each instance of a
payment. The the money opposite signs (positive / negative)"-
You mean that I may record automatically the new invoices for a new month to
be paid by all current students and then use those (the same) records to
record the ReceiptNo? (it has to be accounting correct)
Or shall I link the Invoices with another table (tbl_Receipts). Because I
may have some invoices prepared but finally will not be paid, due to the fact
that some students may discontinue?

Best Regards,

GeorgeCY





Ο χÏήστης "Fred" έγγÏαψε:

 

[Fred]

Hello George,

An overall answer would get into more complicated questions regarding your
invoicing, but here are a few thoughts:

My idea was to put invoices (actually, invoice line items) and payments in
as independent records in the same table.


To get much more specific we'd need to know many more details about your
intended invoicing, and then dependent on those what you might need could
vary from just some advice to ongoing help on a larger more complex
application development. Here are a few of the questions, plus a few
imaginary answers which could lea to the example below that:

Are you an experienced enough developer to take on building a more complex
application? No

Is your school small enough where some (leveraged) manual entry of the
invoices is feasible? (less sophisticated/complex/automated= the low tech
Fred way) ) Yes

Is you applicaiton supposed to recognize and enforce the concept of
invoicing being a one-time "event? No

Do your intend to just invoice customers for all of the months in advance
for a signed up course? Yes

Are you keep the "accounting" aspect of this narrow e.g. just recording
invoicings and payments which cover them? Yes

Make and InvicesPayments table. Each record would typically be an invoice
line item for a month of attendence in a class, or a (n application of all or
part of ) a payment to cover that invoice line item. Table should include:

Class ID
StudentID
Amount (let's say invoices are a positive number and payments are a negative
number)
Description (this might be just something that gets concantated to the class
name such as the month of instruction that this line item is for.
Notes
DateInvoicedOrPaid

When a student signs up for a class you enter one invoice line item for the
first month and just copy it for the other future months and just change
the month name etc. accordingly.

If yo receive a payment for two months, you'll break it into two entries for
the months that it covers.


Hope that helps provide a foundation to do what you seek to do.

Sincerley,

Fred

 

[George]

Hello dear Fred, thanks again for your helpful information,

Well, I have spent a lot of time to find the best answer. But I needed to
test my self and also to deliver a professional solution, so what I have now:

tbl for classes
tbl for classes of each academic year (recodring also the amount to be
charged)
tbl for students (it gets the amount to be charged from above - although
some discount may be applied)
tbl invoices (related one to many to students) and
tbl receipts (related on to many to invoices) - I have to think also the
near future - perhaps someone will pay for a part of an invoice.

I now automatically record invoices for all current students and then I
record (most of the details automatically) the receipt and print it.

I am able also to keep track of invoices and know who has to pay.

Waww, I never thought that was going to drive me crazy, but finally I got it.

Thanks again for your valuable information Fred,

Best regards,

GeorgeCY

Ο χÏήστης "Fred" έγγÏαψε:

 

[Larry Daugherty]

You're over the worst but there's still a little way to go. Comments
inserted inline with your last. Note that I use a convention of
"objEntity" in my comments.

--
-Larry-
--

Hello dear Fred, thanks again for your helpful information,

Well, I have spent a lot of time to find the best answer. But I needed to
test my self and also to deliver a professional solution, so what I have now:

tbl for classes
tbl for classes of each academic year (recodring also the amount to be
charged)

This table should be something like tblClassesSessions. The academic
year is an attribute within the record. Absolutely, positively do
*not* encode data in a table name such as tbl2008.

tbl for students (it gets the amount to be charged from above - although
some discount may be applied)

tblStudent should *not* get an amount to be charged. The only things
that should be entered in a student record are those things that are
relatively unchanging over time and that apply to this student alone.
You might have the same student in more than one academic year. I
would hope that you do. You don't want to have to purge and change
records just because you have new data.

You are trying to model a many-to-many relationship between tblStudent
and tblClassesSessions. That requires a junction table. That
junction table should be named something like
"tblStudentClassSession". That table will indicate which Students
were enrolled in which Class Sessions. That's where the amount due
should be. That's where you would have notes and other details about
that Student in that Class Session.

I won't mess with your invoices, statements, payments, etc.

tbl invoices (related one to many to students)

and Students is one to many with tblClassSession where
AcademicYear="2008" or something like that.

and

 

[George]

Thanks a lot Larry for your comments, - Commnents inserted inline,

Ο χÏήστης "Larry Daugherty" έγγÏαψε:

You're over the worst but there's still a little way to go. Comments
inserted inline with your last. Note that I use a convention of
"objEntity" in my comments.

--
-Larry-
--



This table should be something like tblClassesSessions. The academic
year is an attribute within the record. Absolutely, positively do
*not* encode data in a table name such as tbl2008.

Each record of student is related to a certain class offered in Academic
Year. Customer wants to add each student every year either is a new one or
not. Students always fill a registration form for each year.

tblStudent should *not* get an amount to be charged. The only things
that should be entered in a student record are those things that are
relatively unchanging over time and that apply to this student alone.
You might have the same student in more than one academic year. I
would hope that you do. You don't want to have to purge and change
records just because you have new data.

As mentioned above, plus that a student this year may be charged the total
amount which is the charge amount of a certain class, but next year he/she
may get a discount (more than one student from the same family - or even
students which are relatives to the customer and so she will not charge those
students.)

You are trying to model a many-to-many relationship between tblStudent
and tblClassesSessions. That requires a junction table. That
junction table should be named something like
"tblStudentClassSession". That table will indicate which Students
were enrolled in which Class Sessions. That's where the amount due
should be. That's where you would have notes and other details about
that Student in that Class Session.

As above - each student to be recorded every year.

I won't mess with your invoices, statements, payments, etc.

Do you mean that is better not to create the invoicing/payment system?

 

[Larry Daugherty]

In line again...

--
-Larry-
--

Thanks a lot Larry for your comments, - Commnents inserted inline,

? ??????? "Larry Daugherty" ???????:


Each record of student is related to a certain class offered in Academic
Year. Customer wants to add each student every year either is a new one or
not. Students always fill a registration form for each year.

Unless the customer is serving as the lead developer then the
following applies. Note that if s/he is then it's his responsibility
for the time and other resources consumed in the delivery of the
desired outcome: It's the customer's business what business
information will be contained in the application and what information
will be retrieved and dispalyed. It most definitely *is not* the
customer's business to micromanage and to try to dictate the elements
of the design! That's *your* business as the developer. The design
of the schema; the tables and their relationships should never even be
seen by the customer except possibly to pass your design for
evaluation to another person who is conversant with Relational
Database design rules and practices. If you let your customer(s)
dictate the design of your schema you'll have lots of work ahead of
you trying to find work arounds to achieve desired results from
dysfunctional designs. All good database applications are built on
the foundation of a good schema. Get it right and you'll have an
extendable and maintainable application. Get it wrong and you'll
spend a lot of time "going back and doing it over" again and again and
again.

As far as students filling out a new registration card each year, so
what? Take the stack of completed registration cards to the computer
with the application. For those students already in the database,
most or all of the data will remain the same and will not require any
new typing with chances of typos, etc. Just enter new or changed
information. Remember, each record in the Student table should have
information only about the individual student.

Their tegistrtion information and fee information belongs elsewhere
and will require data entry. That information is new for this year.
There is no reason you couldn't have all of the required information
show at the same time ... Remember that you view and massage data
from Forms, not Tables. Users (including Customers) should never have
unhindered access to the tables.

As mentioned above, plus that a student this year may be charged the total
amount which is the charge amount of a certain class, but next year he/she
may get a discount (more than one student from the same family - or even
students which are relatives to the customer and so she will not charge those
students.)

What the customer will charge for a student is new information. If
you have different business rules for different situations then those
rules bust be applied as appropriate.

Note that I didn't pick up on any relationships being tracked in the
application for a student's relatives. Somehow that association must
be tracked and you must create and apply the business rules.

As above - each student to be recorded every year.

If you persist in ignoring advice received here, why post here looking
for help? We'll try to help you to move in the more correct
directions. I won't help you dig a deeper hole in the road.

Do you mean that is better not to create the invoicing/payment

system?

No. It is that the "money" systems can enjoy a world of their own.
There are 'invoices', 'statements', payments', etc. to consider.

The general rule of these newsgroups is that there's a limit of a
single MS Access technical issue per post. When that issue is
resolved then that thread is done. If the same individual has more
issues then s/he should post each of them in its own thread.

Your thread has gone far beyond that. No harm, no foul. It's just
that it has gone on far enough to start applying the brakes. This is
especially true since you apparently don't intend to heed the advice
you receive here. We're all volunteers here with the motivation to
help other Access developers resolve their technical issues. Some
folks equate "free" to "worthless" and behave accordingly.

If you intend to learn to develop applications with Access then this
is a great newsgroup for Access newbies to lurk. Another is

microsoft.public.access.gettingstarted

There is also a great site that you should visit: www.mvps.org/access

 

[George]

Thanks a lot Larry for your comments.

I want to let you know that I didn't mean by any way to offend you or any
other guy helping here. I know that everyone here (at least the majority)
tries to help and I am very grateful for your valuable help - So, sorry for
the missunderstanding.

GeorgeCY


Ο χÏήστης "Larry Daugherty" έγγÏαψε:

 

[Fred]

Happy to help!


Sincerely,

Fred

Hello dear Fred, thanks again for your helpful information,

Well, I have spent a lot of time to find the best answer. But I needed to
test my self and also to deliver a professional solution, so what I have now:

tbl for classes
tbl for classes of each academic year (recodring also the amount to be
charged)
tbl for students (it gets the amount to be charged from above - although
some discount may be applied)
tbl invoices (related one to many to students) and
tbl receipts (related on to many to invoices) - I have to think also the
near future - perhaps someone will pay for a part of an invoice.

I now automatically record invoices for all current students and then I
record (most of the details automatically) the receipt and print it.

I am able also to keep track of invoices and know who has to pay.

Waww, I never thought that was going to drive me crazy, but finally I got it.

Thanks again for your valuable information Fred,

Best regards,

GeorgeCY

Ο χÏήστης "Fred" έγγÏαψε:

 

[cris cris]

hey fred! i am a student in the second year to digital arts and computing and to the computing part i am doing also databases and sql and now i have a school project to create a database. As i am at the beginning and I am looking for ideas and solutions I decided to inspire from georg idea because anyway we are not in the same country and my project won't be public...it's just for school. I hope he won't mind.
I m not so good in sql i recognize by reading your discussion i was wondering if i can do like this: have a table of students with:

student id
name and surname
registration date: and here to have year 2008 and 2009; i also need to have a story of my database so i will say my cousin graduated and opened last year a dance school and she would need a database; that's why i was thinking to have 2 years included, i mean one that passed

then a table of classes with:

class id number
list of dance classes
description column: a very short inf about each course

tables of students registered in a class:

student id number
class id number
data they started

i am not so sure here how to do


and about the last table...i was thinking the payment one but can i just write the

class id
student id
amount per month

and if they paid only for one month or for two months or for the whole year....because you were talking about information in the tables that can be updated and i didn't properly understand....

My idea is quite messed up right now but i made a short plan to start putting all in order, Can you help me a bit define everything but please be gently in explaining because I am at the beginning. The idea is that first I really need to have the plan written and then start doing. I need at least 4 tables and clear information…..it doesn’t have to be a complicated database but to contain information and being possible to create select statements. I would appreciate your help. Thankss!


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng