DB Design PLEASE PLEASE HELP! 1

[AccessApprentice]

Can someone please help me with this database I am I trying to create. I am so confused I just dont know what to do. I have to create a database to manage the swimming courses that are offered and the students that are taking those classes. I have created a several tables. I am trying to figure out how to create a way to determine how much each client owes for each class that they register for. I think I have created the tables properly but right now I'm not sure of that either. This is all very new to me. Can someone look at what I have so far and give me some type of help as to which direction I should take.

 

[Guest_imported]

THE TABLES AND THEIR FEILDS I HAVE ARE AS FOLLOWS:

CLASS = "ClassID";"Term";"Time";"Days";"Beginning Week";"Ending Week";"InstructorID";"Price";"RegistrationFee";""

CLIENTELE = "ClientID";"LastName";"MiddleName";"FirstName";"DateOfBirth";"Address";
"City";"State";"Zip";"PhoneNumber";"AlternatePhoneNumber";
"EmergencyContactName";"EmergencyContactRelationship";
"EmergencyContactPhone";"DoctorName";"DrOfficePhone";"Allergies";
"Medications";"Medical Problems";"ParentID";"Notes";"Level";""

LEVEL = "Level";""

CONCATENATEDTABLE = "ClientID";"ClassID";"RegistrationID";""

PARENT = "ParentID";"LastName";"FirstName";"BillingAddress";"City";"State";"Zip";
"PhoneNumber";"EmailAddress";""

PAYMENTS = "PaymentID";"ClientID";"PaymentDate";"PaymentMethodID";"PaymentAmount";
"CreditCardNumber";"CardholdersName";"CreditCardExpDate";"RegistrationID";
""

INSTRUCTOR = "InstructorID";"Social Security Number";"FirstName";"LastName";"StreetAddress";"City";"State";"Zip";
"PhoneNumber";"AlternatePhoneNumber";"DateOfBirth";"EmailAddress";""

PAYMENTMETHODS = "PaymentMethodID";"PaymentMethod";""

 

[dhookom]

Your table structure doesn't look to bad. A couple points:
If you have the RegistrationID in the Payments table, you shouldn't also need the ClientID (unless I am reading your tables wrong).

Also, you are allowing for only one instructor per class.

Since you are just starting out, find a good naming convention that doesn't allow spaces in field names. Also, "time" is a function so consider using "ClassTime".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jsteph]

I'm not sure how deep you want to get in this, but in addition to dhookum's advice about the one instructor per class, I think Parent also requires a many-many link table. One parent may have many children in different classes, and each child more often than not has two parents. In this case, the third 'link' table could have a 'primaryparent' boolean field or something similar.

The 'concatented table' otherwise know as 'associative' or 'link table' would serve as the example for the Parent and Instructor many-many link. For Parent, the ParentID and CLientID would be paired as a unique key for that table. I'm assuming you're using 'RegistrationID' in the link table for Client/Class as an alternate key? If you're going that route, then ClientID is redundant in the Payment table, since you can always derive it from the link table. Unless you're allowing a client other than the one taking the class to make a payment, which, while possible is probably not your intent.

You could also (again--not sure how deep you want to get) normalise the credit card info out of the Payment table, so all those card-related fields need only be enterred once, and following payments only need the card#.
--Jim

 

[AccessApprentice]

Ok. Thank you. I took the ClientID out of the Payments table. Each client will only have one Parent. How do I create a way to determine how much each client owes for each class that they register for?

 

[jrbarnett]

AccessApprentice

Not sure where you are based geographically, but in certain parts of the world, storing credit card details after the transaction has gone through is frowned upon, especially if you aren't using any form of encryption to store the card number and expiry date.
If you do have a need to store the details of the card used for the transaction after it has gone through, I wouldn't store the number whole, just the last 4 digits (which would be enough for the cardholder to identify which card they used). On the form you can always asterisk out the other numbers, which is common practise here in the UK now on receipts.

John

 

[jsteph]

Ok, now the scope is growing. With billing systems, normally you'll have, at the simplest, an Invoce record, but typically this will have Invoice Items. The Items might be a Class, a Registration Fee, or they coould be combined into one item, or you could do away with the Items and just have a single Invoice record with a total of what they owe and a description. If this is a simple system that should suffice, but as the scope grows, you may want to build-in some infrastructure to support invoice items.

It gets more complex here. As an example, the company I work for may send a customer 3 invoices in one week, say one for 500, one for 1500, and one for 700. Next week we get an envelope with just check for $1100 from the customer. How do we post this? Why the odd $1100 instead of an even $500 for the first invoice, and then maybe a separate check for 1500 for the next invoice, and so on? Oh, if it were that easy. We typically just post against the earliest invoices in order and let it build. But then a month later the customer sends $1500 and says he wants to be considered paid in full for the month: He explains the 1100 was 500 for the first invoice and 600 for the 3rd (the 700 invoice)--he had a problem with the 3rd product and was short-paying by 100, and he was saving up until he could gather the 1500 for the middle invoice. And that's not uncommon or even close to the complexity it can get to. You obviously would want to expect full payment, but many businesses have a delicate balance with certain customers and have to accept what they can get.

Sorry to digress, but you can see how complicated the AR game can get. In your case I'd keep it simple and build a simple Invoice table, then a query summing Total Invoices by client--maybe display that on a form, and have a field calculating the total of all payments for that client, the difference being his current balance.

Again, you can get very complex with this and build many more tables into the infrastructure. Our financial system, SAP, has about 17,000 tables for the financials alone--the number of tables in the entire system is into 6 figures. So if you can keep it to 8 or 9, you're doing well!
--Jim

 

[AccessApprentice]

build a simple Invoice table, then a query summing Total Invoices by client--maybe display that on a form, and have a field calculating the total of all payments for that client, the difference being his current balance


Ok..thank you so much for your input..this sounds like what I'm looking to do....the problem is...I am new to this and dont have a clue as to where to start.

I think you are telling me to build a new new table:INVOICE that links back to the CONCATENATEDTABLE with the following fields: ClientID";"ClassID";"RegistrationID";""

If this is not correct can you tell me whtat fields should I include in the table and where should they be linked(related) Again I am just starting out with this so I'm sorry if I am asking questions that I should already know the answers to.

 

[jsteph]

Yes, at the simplest level you'd have an Invoice table with each Invoice record linking to a single RegistrationID in the concatenated table.

So from the Invoice table, you have a FK RegistrationID (this limits you to one registration per invoice, but it's probably the best to keep it simple), from which you get the ClassID to fetch the price + reg fee. But--this InvoiceAmount should be it's own field in the Invoice table--not linked to the Price/Regfee in Class table--since the price and reg fee may change over time. This is considered 'transactional data', and realistically, most of the fields in the Invoice table are transactional, and are what you would normally consider 'redundant' in a normalized db.

For instance you want the Invoice table to have it's own ClientName and Address (fetch initially from the client table), since the address may change, and for record-keeping purposes you want to know the original name, address and price. If the address changes, of course, this requires you to update the invoice record only if it hasn't been closed, yet if the price changes in the Class table, obviously you don't have that automatically updating the invoice because that's not the price which was agreed upon. But this still gives you full historical reporting on a per-client basis, since you can still summarize all invoices per client based on his current name, address, etc, even though that info in the Invoice table may be different.

Then each Payment record you'd create as you recieve a check from a client. You typically post each payment against an Invoice record and decrement a field in the Invoice table such as DueAmount (a separate field from InvoiceTotal), or alternatively increment a field PaidAmount--either way you close the invoice when the Due is zero or Paid=InvoiceTotal.

That's a very simplified way...but I'm not sure--as in my previous example--what a 'typical' payment is for you and if that will suffice. You may want to set up AR accounts for each client, where all payments from a customer go into this big pool. Then you post from this account to invoices. As in my previous example, this often leaves odd amounts of 'un-applied cash' in the customers accounts, or--more frequently in our case--underpaid invoices. In these cases we generate Credits, which would be like a Payment record, but it's where we wash the invoice so it can be considered Paid--without reducing the total invoice amount.

This all gets into Accounting 101, a field in which I'm not well versed at all, but I have a basic idea of what our systems do because I have to sift through thousands of tables to generate reports for our financial team. And I sure wish it were as simple as what you've got here!
--Jim

 

[Guest_imported]

Ok I have made some changes to my tables. I think I am still missing something though. I am trying to follow the advice you gave in the last post. Can you tell me if have my tables structured correctly.



THE TABLES AND THEIR FEILDS I HAVE ARE AS FOLLOWS:

CLASS = ClassID"";"Term";"Time";"Days";"BeginningWeek";
"EndingWeek";
"InstructorID";"Price";""

CLIENTELE = "ClientID";"LastName";"MiddleName";"FirstName";"DateOfBirth";
"Address";"City";"State";"Zip";"PhoneNumber";"AlternatePhoneNumber";
"EmergencyContactName";"EmergencyContactRelationship";
"EmergencyContactPhone";"DoctorName";"DrOfficePhone";"Allergies";
"Medications";"MedicalProblems";"ParentID";"Notes";"Level";""

LEVEL = "Level";""

CONCATENATEDTABLE = "ClientID";"ClassID";"RegistrationID";"RegistrationDate";""

PARENT = "ParentID";"LastName";"FirstName";"BillingAddress";"City";"State";
"Zip";"PhoneNumber";"EmailAddress";""

PAYMENTS = "PaymentID";"RegistrationID";"PaymentDate";"PaymentMethodID";
"PaymentAmount";""

INSTRUCTOR = "InstructorID";"SocialSecurityNumber";"FirstName";"LastName";
"StreetAddress";"City";"State";"Zip";"PhoneNumber";"AlternatePhoneNumber";
"DateOfBirth";"EmailAddress";""

PAYMENTMETHODS = "PaymentMethodID";"PaymentMethod";""

INVOICES = "InvoiceID";"RegistrationID";"DueAmount";"PaidAmount";""



The tables are related as follows:

Class.ClassID 1:M ConcatenatedTable.ClassID

Class.ClassID M:1 Instructor.InstructorID

ConcatenatedTable.ClientID M:1 Clientele.ClientID

ConcatenatedTable.RegistrationID 1:M Invoices.InvoiceID

Clientele.Level M:1 Level.Level

Invoices.RegistrationID Indeterminate Payments.RegistrationID

Payments.PaymentMethodID M:1 PaymentMethods.PaymentMethodID

 

[jsteph]

You might want to call the concatenated table the 'Registration' table, since that's its main focus.

The Payments table should probably not have RegistrationID, but instead have InvoiceID, since customers will probably pay based on an Invoice, which will have one (in this simple example) a single RegistrationID. So you can always navigate from a Payment to the proper registration record through the Invoice table.

In the Invoices table, you'd probably have either Due or Paid, not both, but you'd first have InvoiceTotal--the original amount which never changes. The Due or Paid are really calculated fields based on the sum of the records in Payments table for that Invoice against the Total. Though 'purists' would suggest those fields shouldn't exist in the invoice table because they're calculated, here in the real world we can have redundant fields and not feel bad about it. Just make sure you carefully watch the logic of how & when you update that field, so it stays in sync with the actual sum of payments.

Again, in a more involved system we'd create payment transactions which would decrement the DueAmount and then whatever that due amount was we'd have a paper trail showing how we got there. But in this case just make sure you're careful about when it gets updated, and also about what you do when it hit's zero, such as flag it as paid and lock it from further transactions.
--Jim

 

[AccessApprentice]

jsteph....THANK YOU THANK YOU THANK YOU for all your help. I did everything you just said. Now to calculate my AmountDue feilds is this where my query comes in?

 

[jsteph]

A very simplistic approach would be to have a Payments form based on the payments table. As you enter a payment, on the AfterUpdate event (which will catch inserts and changes as well) and AfterDeleteconfirm, just do a simple update of the Invoice table, like:

DoCmd.RunSQL "UPDATE tblInovices SET PaidAmount = DSum(""paymentamount"",""tblpayments"",""invoiceid = " & Me.Invoiceid & """) WHERE Invoiceid= " & Me.Invoiceid

Be careful of the quotes, it's important that you use the double-double-quotes and triple-double-quotes as shown. Also, wrap it with docmd.setwarnings false/true.

And with the AfterDeleteConfirm (if you're deleting a payment that was misposted, check bounced, etc) you'll need to store InvoiceID in a form-level variable since I'm pretty sure me.Invoiceid is gone then.
--Jim

 

[jsteph]

AA,
So it looks like I can't post email address, so if you can pinpoint the area that isn't working and maybe post the code or query sql I might be able to help then,
--Jim

 

[PHV]

So it looks like I can't post email address
Please, read the last lines of your profile:

http://www.tek-tips.com/userinfo.cfm?member=jsteph