Partial Payment Schema

[dickersonka]

i am working on a home project for basically multiple members to share payments, or for one member to pay and the other members pay them back
the concept is sort of like if you have a roommate and you pay all the bills, then he would pay half and i need to keep track of which bills that the roommates have paid

i am leaving a lot of the non critical pieces out for clarity

please advise on my schema

bills
BILL_ID
AMOUNT
PAID
PAID_BY_MEMBER_ID

billmembers
BILL_MEMBER_ID
BILL_ID
MEMBER_ID

billpaymentrequired
BILL_PAYMENT_REQUIRED_ID
BILL_ID
MEMBER_ID
CREATE_DATE
REQUIRED_AMOUNT

billpaymentsmade
BILL_PAYMENT_MADE_ID
BILL_PAYMENT_REQUIRED_ID
PAYMENT_AMOUNT
PAYMENT_DATE

members
MEMBER_ID
MEMBER_FIRST
MEMBER_LAST

[timothybard]

This is how I would approach the issue. I would have one table to track the bills, one table to track the people and one table to track the transactions:

Bills (ID, amount, date billed, date due, etc)
People (ID, Name, etc)
Transactions (ID, Amount, Date, Person ID, Bill ID)

As bills come in, they are recorded in the Bills table. In addition to that, records in the transaction table are generated with negative Amounts to show how much each person owes. As they make payments, records are added to the Transaction table with positive amounts. If a person makes more than their share of a payment, they will have a positive balance (sum of their Amounts in the Transaction table). To reimburse the person who covered the payment, everyone makes their payment and there is a positive balance in the entire account (sum of everyone's amount in the Transaction table). As a result, the person who covered the payment can "withdraw" their money from the account, which would be a negative record in the transaction table and reimburse him for the initial payment he made. The advantage to this system is that it allows the person to keep his money in the "account" and use it to pay future bills.

[dickersonka]

You were the one I was hoping would help me out, you have excellent schema designs on about evreything

have one more small issue with this
lets say you have 2 other people as roommates, but only 2 of those people are required to pay the amount and 3rd has no part in it

would the bill members table make sense then?

billmembers
BILL_MEMBER_ID
BILL_ID
MEMBER_ID

also i think this would add the flexibility to store a percentage amount, if the amount is not always 50 50

then pull from this table before creating the transactions to reflect appropriate amounts

what are your thoughts on this?

[timothybard]

Yes, my original suggestion did not take into account how to divide the bill. Someone would have to make that determination each time a bill was entered.

I like your idea of the billpeople table; however, I'm wondering if it would be better to add a Bill Type field to the Bill table and then using the following table to split out bills:

BillPeople (ID, BillType (FK), Person (FK), percent due)

With the bill type field, you are able to split out the bill based on a percentage for each type of bill instead of having to split it out for each individual bill.

The Bill Type can be water bill, rent, electric bill, etc.

[timothybard]

Just to expand on my previous comment... you could then put some code together so that after a bill is recorded, the database breaks out the bill and adds transaction records based on how the bill needs to be broken out. You could put a query together to do that instead if you are not as comfortable with coding; you just need to make sure the query is run after the bill is recorded.

[dickersonka]

i see what you are saying, sort of one of those things where it seems like it can go either way

the only reason i would be against splitting it out on a bill type, would be if one of the roommates left, but i guess there could be an active flag or something similar on billpeople

[dickersonka]

yeh i am fine with the coding side or database side, just running into this payment thing from multiple people, that may be different people monthly, and percentages that was driving me up the wall

could use a trigger to create the amounts in the transactions table, but i would probably just do it code side, so they can be adjusted if one member does need to pay more

i just don't want to find myself in a bind down the road, because a roommate left or different ones pay different bills

[timothybard]

If a roommate leave, you can update the BillPeople table to redistribute the amount owed; once any individual bill is split up, there's no need to refer to the table except for future bills. Of course, an active flag can be used as well; that would allow you to keep records for historical purposes.

[dickersonka]

very true

thanks so much for making everything clear and being extremely helpful