SQL for a Household Bill splitting db

Question

I'm trying to build a mySQL database for a PHP page that will allow users to enter the cost of a bill and select who in the house it is split with. The problem I'm having is I can't figure out how to calculate the total owed between residents, taking account of what they have paid and what they need to pay someone else.

I want it to be able to handle variable numbers of users 'residents' so have designed the tables like so:

Users Table:

 UserID | User    
 -------+---------
 1      | Jon
 2      | Boris
 3      | Natalie

Bills Table:

 BillID |  BIll  |  Amount  |  Paid By (UserID F.Key)
 -------+--------+----------+--------------
 1      | Gas    | £30.00   |  1 (Jon)
 2      | Water  | £30.00   |  1 (Jon)
 3      | Tax    | £60.00   |  2 (Boris)
 4      | Phone  | £10.00   |  2 (Boris)

So this table shows that the Phone Bill was £10.00 and Boris paid the Phone company for it.

Bills-Users Liabilities Junction Table:

 UserID_fKey | BillID_fKey 
 ------------+--------------
 1 (Jon)     | 1 (Gas)
 2 (Boris)   | 1 (Gas)
 3 (Nat)     | 1 (Gas)
 1 (Jon)     | 2 (Water)
 2 (Boris)   | 2 (Water)
 3 (Nat)     | 2 (Water)
 1 (Jon)     | 3 (Tax)
 2 (Boris)   | 3 (Tax)
 1 (Jon)     | 4 (Phone)
 2 (Boris)   | 4 (Phone)

The Bill-Users table is used to describe who is liable for each of the bills. So, for example Natalie never uses the phone so doesn't need to pay Boris anything for it. The phone bill therefore is split between Borris and Jon. Jon therefore owes Boris £5.

I think this is the best way to do it but I can't work out how to generate a table for each user when they login that shows what they owe one another. To do this (for example) I need to add up everything Jon has paid for, what everyone has paid for on behalf of Jon and work out the balance. Obviously this needs to scale so that there could be 3 or more users in the system.

This is the result I would like to get to:

(eg: Logged in as Jon)

 User    | Amount Owed
---------+-------------
 Boris   | -£15.00
 Natalie |  £20.00

Many Thanks in advance.

Answer 1

you need an owe field in your bill table in order to do bottom bit.

select individual user

SELECT Users.User, Bills.BIll, Bills.owe, Bills.Amount
FROM Users
LEFT JOIN Bills ON Users.UserID = Bills.UserID
WHERE Users.UserID =1

select all users

SELECT Users.User, Bills.BIll, Bills.owe, Bills.Amount
FROM Users
LEFT JOIN Bills ON Users.UserID = Bills.UserID
LIMIT 0 , 30

select all users sum of paid

SELECT Users.User, Bills.BIll, sum(Bills.owe), sum(Bills.Amount), sum(Bills.owe)- sum(Bills.Amount) as arrears
FROM Users
LEFT JOIN Bills ON Users.UserID = Bills.UserID
GROUP BY Users.UserID;

demo

Answer 2

In the end I achieved this by using a function. It probably wouldn't scale to 100's of users very well but still gives me the flexibility I wanted. I'm trying to start an SQLFiddle to demo it but having issues.

Set @CurrentUser = '1' (Jon)

Select User, funcAmountOwed(@currentuser,UserID) from Users
WHERE UserID != @CurrentUser;

Returns:

 User    | Amount Owed
---------+-------------
 Boris   | -£15.00
 Natalie |  £20.00

The function is something along the lines of:

funcAmountOwed(@CurrentUser, @CurrentDebtor);

SET @AmountPaidByCurrentUser = (

Select SUM(Amount) from Bills b
INNER JOIN BillsUsers bu ON b.BillID = bu.BillID_fkey
WHERE b.PaidBy = @CurrentUser and bu.UserId_fkey = @CurrentDebtor)

SET @AmountPaidByDebtor = (

Select SUM(Amount) from Bills b
INNER JOIN BillsUsers bu ON b.BillID = bu.BillID_fkey
WHERE b.PaidBy = @CurrentDebtor and bu.UserId_fkey = @CurrentUser)

RETURN @AmountPaidByCurrentUser - @AmountPaidByDebtor;

I had to nest another function in there to divide the Amount by the Number of people the Bill was split between too but as above I can't remember that from memory without SQLFiddle.