用于家庭账单拆分数据库的 SQL [英] SQL for a Household Bill splitting db

问题描述

我正在尝试为 PHP 页面构建一个 mySQL 数据库，该数据库将允许用户输入账单费用并选择与房子中的谁分摊.我遇到的问题是我无法弄清楚如何计算居民之间的欠款总额，考虑到他们已经支付的费用以及他们需要支付给其他人的费用.

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:

用户表:

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

账单表:

 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)

因此该表显示电话费为 10.00 英镑，鲍里斯为此支付了电话公司费用.

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)

Bill-Users 表用于描述谁对每个账单负责.因此，例如 Natalie 从不使用电话，因此不需要为 Boris 支付任何费用.因此，电话费由鲍里斯和乔恩平分.因此，乔恩欠鲍里斯 5 英镑.

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.

我认为这是最好的方法，但我不知道如何在每个用户登录时为他们生成一个表格，显示他们彼此欠的钱.为此(例如)我需要将 Jon 支付的所有费用、每个人代表 Jon 支付的费用加起来并计算出余额.显然，这需要扩展，以便系统中可以有 3 个或更多用户.

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.

这是我想要的结果:

(例如:以 Jon 的身份登录)

(eg: Logged in as Jon)

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

非常感谢.

推荐答案

最后我通过一个函数实现了这一点.它可能不会很好地扩展到 100 名用户，但仍然给了我想要的灵活性.我正在尝试启动一个 SQLFiddle 来演示它，但遇到了问题.

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;

返回:

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

该函数类似于:

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;

我不得不在其中嵌套另一个函数，以将金额除以账单所分配的人数，但如上所述，如果没有 SQLFiddle，我无法从记忆中记住这一点.

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.

这篇关于用于家庭账单拆分数据库的 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！