Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Database Structure & Business Practice w/Joint Accounts

futilityfutility Registered User, ClubPA regular
edited August 2010 in Help / Advice Forum
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?)

futility on

Posts

  • vonPoonBurGervonPoonBurGer Registered User
    edited August 2010
    You could add a linking table between parent records and child records (i.e. a Family table). Then it would be easy to do a query that sums up all the payments owed by a family, and present to total as a single bill. Then a combined payment goes against that total and it's all very neat and tidy. That sort of linking table allows a many-to-many relationship between parents and children, which is probably what you want since parents can have more than child, and vice versa.

    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • Dr. TrevorkianDr. Trevorkian Registered User
    edited August 2010
    You're stumbling because you don't have the usual billing or ordering abstraction in play. That is, each bill or purchase is a collection of line items.

    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.

  • futilityfutility Registered User, ClubPA regular
    edited August 2010
    I'm working to build line items in; though preventing my god damned computer illiterate users from over attributing payments to a particular line is harder than I thought.

  • mightyjongyomightyjongyo Registered User regular
    edited August 2010
    Could you do it based on last names/families? Like, each family has an account, and you can have a 'students' entry which would contain structures for each student in that family and their classes. Then you would only have one payment/balance field.

    Probably not feasible though, since you'd have to re-do the whole database.

  • futilityfutility Registered User, ClubPA regular
    edited August 2010
    Unfortunately not, people have the same last name. I'm also not afraid of redoing the database. The hard work is in scripting and layouts and I'm not there yet

  • DeebaserDeebaser Lead Frog Rammer Fake Board GamerRegistered User regular
    edited August 2010
    Really simple quick and dirty answer is to pull everything back to a parent table. If you associate every child by last_name and phone number you shouldn't run into any problems.

    Edit: wow...double beat'd

  • splashsplash Registered User
    edited August 2010
    I'd think what vonPoonBurGer solves the problem?

    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.

  • PailryderPailryder Registered User regular
    edited August 2010
    yeah, call it a billing table. add a billing id to your child records that links to the master record. It's like having a guarantor or master account that can be linked to any number of accounts.

    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!

    steam_sig.png
    3DS Friend Code: 0705-3757-3938
Sign In or Register to comment.