I'm having a problem wrapping my head around this and was wondering if I could get some help.
I'm building a database to handle kids taking classes. As I have things built each kid basically acts as an account. They live in their own table and are linked to classes, payments and parents (in a contact table) all linked through primary keys.
One kids takes classes and pays off those classes... easy enough right.
Unfortunately kids have these damn things called siblings, so as things go one parents pay for say 2 accounts with 1 check and we enter that 1 check into the system 2x. I see that as kind of bad because we now have 2 records for 1 payment. Also as it currently stands, only 1 kid can be linked to infinite parents because I can only really have 1 key linking the individual records in 2 tables.
So...
How can I make 2 accounts act like 1? Do I put a kind of joint table between some table relationships? Ideas?
(i'm building this in filemaker, but I figure the db structure philosophy should universally be the same... right?)
Posts
For your situation, *most* customers will only ever have one item (kid's tuition or whatever the term is) on their bill, but in some cases there will be more.
There are other ways to handle it but that's probably the most common approach.
Probably not feasible though, since you'd have to re-do the whole database.
Edit: wow...double beat'd
So it creates a link which is a many to many relationship so that one child can have many parents and one parent can have many children. Isn't this correct?
I was gonna say if you are thinking of tracking kids differently how about by home address. But there will be cases where the parent doesn't live in the same home.
The only problem you will run into (and its not necessarily a problem just something to be aware of) is that you could create duplication in your billing table. For instance, you could have three children in a family and one child is paid for by parent_A, another child paid for parent_B, and the third child is paid for by Parent_C who happens to be the "same" person as parent_A but they want a seperate bill.
In other words, you may create bob smith three times if he wants three bils, or he could only be there once for all three kids. The problem will come from a usage standpoint, if a new child is added, which billing person (bob smith) get's picked, or do we now have four bob smith?
what i'm getting at is, can a person exist as a billing entity more than once? what will be your unique identifiers, a concatanation of last/first/address or some other identifier (can you use social security numbers and make them unique)?
good luck!
Blizzard: Pailryder#1101
GoG: https://www.gog.com/u/pailryder