Redesigning My Database - Need to Know the Best Way

[Guest]

I have a business providing language classes for children. The customers are
the parents who sign their kids up for the classes. The tables I have now
are:

ParentInformation (ParentID#, name, address, etc.)
ChildInformation (ChildID#, Parent#, name, birthday, etc.)
SignUps (SignUpID#, ChildID#, Session, Class, Tuition, etc)
Payments

There is a one to many relationship from ParentInformation to
ChildInformation since a parent can obviously have many children. There is a
one to many relationship from ChildInformation to SignUps since a child can
sign up for many classes.

I am struggling with the Payments table and how it should be related. I
want it to be related to the Parent database, but the payments should be
applied to the SignUps. A parent can make one payment for all their kids or
multiple payments for just one child.

I want the tables set up correctly so that I can produce meaningful reports
for myself as well as invoices for the parents that show what they ordered
(i.e. what children are signed up for what classes) and the payments that go
against those orders.

Any suggestions on how to set up my Payments table would be greatly
appreciated!!

~Marcy

 

[Jeff Boyce]

From your description, you are anticipating a many-to-many relationship
between payments and parents. Since the parents are the "one" at the head
of the train, any/all SignUps will belong to a Parent record (by the way,
some families have two parents, some families have divorced parents, some
families ... -- as if you didn't have enough to handle/model already!).

I figure you'll need a resolver/junction/relation (many-to-many) table
something like:

trelParentPayment
ParentPaymentID
ParentID
PaymentID
Amount
PaymentDate
...

But wait! There's more!

If you also need to show what portion of what payment was applied against
what SignUp, you'll need a resolver/... table there, too:

trelAppliedPayment
AppliedPaymentID
ParentPaymentID
SignUpID
AmountApplied
... (?DateApplied)

You'll still need to "do the math" to ensure that payments are properly
distributed among Signups.

All things considered, this is bigger than a breadbox, and somewhat
re-invents the wheel. Have you considered buying a commercial accounting
package instead?

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,

That helps a lot and I contemplated the whole thing over the weekend.
You're right that SignUps should belong to the parents and not the child. I
have a couple of followup questions...

1. Did you mean I should have a many-to-many relationship between signups
and payments? If you really meant payments and parents then I'm confused.

2. Are the following relationships correct?
parent to child - one to many
parent to sign ups - one to many
parent to payments - one to many
sign ups to junctiontable - one to many
payments to junctiontable - one to many
payments to signups - one to many

Thanks for all your help!

~Marcy

 

[Jeff Boyce]

Marcy

This is partly art, partly science, so take the notions you get here with
grains of salt...

Based on my understanding (always risky!) of what you described, one Parent
(record) could have zero-to-many (a classic one-to-many situation) Children,
and each Child could have zero-to-many SignUps (records). You probably want
to keep this relationship, so you can keep track of which children signup
for which courses. You don't need to go directly from Parent to SignUp,
because you can still get there via Children.

And a Parent could make zero-to-many Payments. Imagine Parent in the
middle, with the Children-SignUps, and the Payments sticking out like arms.

1. Did you mean I should have a many-to-many relationship between signups
and payments? If you really meant payments and parents then I'm confused.

I did mean you should consider a resolver table between SignUps and
Payments. If, as I understood your description, you want to be able to use
one Payment to "satisfy" multiple SignUps, or multiple Payments to satisfy a
single SignUp, you need to resolve this many-to-many relationship.

In my original thoughts, I did include a Parents-to-Payments resolver table,
but this was because I was thinking about a situation in which more than one
parent might contribute to the same "payment". If you are comfortable with
always designating a single Parent record as the "owner" of a Payment
record, one-to-many will be sufficient.

2. Are the following relationships correct?
parent to child - one to many
parent to sign ups - one to many (actually, via Child, one-to-many/one-to-many)
parent to payments - one to many
sign ups to junctiontable - one to many
payments to junctiontable - one to many
payments to signups - many to many -- and Access doesn't have a mechanism

for this, other than what you just did, via a junction table
Except as noted, yes.

Thanks for all your help!

~Marcy

Good luck!

Jeff Boyce
<Access MVP>

 

[Guest]

OK - got it. That's a big help. Thanks SO MUCH Jeff.