Penny Arcade Forums› Help / Advice Forum
As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

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

  • Options
    vonPoonBurGervonPoonBurGer Registered User regular
    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.

    vonPoonBurGer on
    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • Options
    Dr. TrevorkianDr. Trevorkian Registered User regular
    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.

    Dr. Trevorkian on
  • Options
    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.

    futility on
  • Options
    mightyjongyomightyjongyo Sour Crrm East Bay, CaliforniaRegistered 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.

    mightyjongyo on
  • Options
    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

    futility on
  • Options
    DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered 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

    Deebaser on
  • Options
    splashsplash Registered User regular
    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.

    splash on
  • Options
    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!

    Pailryder on
    Steam: http://steamcommunity.com/id/pailryder/
    Blizzard: Pailryder#1101
    GoG: https://www.gog.com/u/pailryder
Sign In or Register to comment.
Penny Arcade Forums› Help / Advice Forum