Suggested DB Schema for a Lunchtime Reimbursement Application

Suggested DB Schema for a Lunchtime Reimbursement Application

I am designing a web-based smartphone app with jqTouch. The idea is to be able to:

• Track who owes whom lunch
• Track debt over time
• Transfer debt from one person to another

This sounds convoluted, but it will solve a real-world problem for my lunch buddies who often pay for each other and wind up reimbursing through Paypal later. For those who don't, they often trade debt... for instance:

Jason buys lunch for David  David buys lunch for Roslyn  Phillip buys lunch for Roslyn  

So - David owes Jason, Roslyn owes David and Phillip. As a debt settlement, David commutes his debt to Jason and Roslyn buys him lunch as repayment for David and now they're even. The only person left standing is me, and that's pretty much par for the course :)

The real question is...

How can I express this in terms of a relational db? I can track line-item expenditures and users as such:

purchases  =========  purch_id  user_id  amount  location    users  =======  user_id  name  

Do I handle the rest as business logic? How do I track the debt commute? If I wanted to get meaningful reporting out of this, such as expenditures over time, and the average time it takes for a given person to repay -- I would need a more complex schema!

Any thoughts/criticism is welcome! This is a fun mental exercise and is in no way considered a serious project.

Update

Considering the lack of interest in the question as posed - I'm posting up a bounty! Some questions that will need to be addressed to award the bounty are as follows:

• How to track running totals without having to sum a potentially lengthy transaction table
• How to keep track of meta-data for transactions without over normalization (e.g. the 'commuted' debt feature described above

Answer by dleavitt for Suggested DB Schema for a Lunchtime Reimbursement Application

Indeed, I too dig thinking quantitatively about social situations.

You'd want to represent it with three tables:

users

• id
• name

lunches

• id
• location
• date

debts

• lunch_id (a lunch)
• creditor_id (the user who lent)
• debtor_id (the user who borrowed)
• amount

Each "lunch" can have zero or more "debts" associated with it.

Each "debt" has two users (a creditor and a debtor) and an amount.

This setup should make it pretty easy to determine everyone's overall balance as well as the balance between any two users.

Answer by Ashraf Abrahams for Suggested DB Schema for a Lunchtime Reimbursement Application

You could use the layout suggested by dleavitt and combine the lunch_id, creditor_id, debtor_id and amount fields as the Primary Key. This will allow you to add another row using the same lunch_id as long as at least one of the other fields change values. This means you can add a row with the same lunch_id and simply change the creditor_id to commute that lunch to someone else. Similarly you might choose to change the debtor_id in the new row to move the debt from one person to another person. With this setup you could also implement partial payments by adding a new row and entering negative values to indicate payments. Adding these amounts will give me a running total of the amount owed. This should be sufficient to track changes and still maintain historical data. A possible addition might be a datetime field to check when the new row was inserted. Adding this field to the Primary Key also solves the situation where someone commutes the debt and it then gets commuted back to the original creditor later. Here the new row will have a different datetime value so it will be valid.

Answer by Alex for Suggested DB Schema for a Lunchtime Reimbursement Application

I work in a Bank, and I think the fittest sistem to your case is to create a system based on a kind of credit. I'm not sure if the price of the lunch matters to the issue. Supose a friend bought you a fine piece of cake with exotic fruits. And you bought him/her a McChiken. Is the same debt? Whatever the situation (price of the lunch matters or not), a credit system should be a good thing. I'm gonna design the tables able to work suposing that some lunch are more valuable than others. Otherwise, only consider one credit for all the lunch.

t001_partners  prt_id: bigInt  prt_name: string  prt_balance: int    t002_lunch  lnc_id: bigInt  lnc_description: string  lnc_store: string  lnc_credits: int    t003_lunch_x_partner  lxp_buyer: bigint (FK)  lxp_receiver: bigint(FK)  lxp_date: dateTime  lxp_quantity: int  

Now your system is able to (using different kind of queries):

1. Track back when, where and how much lunches each partner have bought/received;

2. No matters who is in debt to those who: if the partner have credit, he is entitled to earn a lunch;

3. If the previous feature doesn't satisfy your third requirement, you should create a procedure to transfer credits between partners through a special ID in t002_lunch table registering in t003_lunch_x_partner table.

4. If all the lunch have the same value, the system fits. If lunch would have different credit, the system fits. You are able even to create a system using currency as a credit (just change the lnc_credits in table t002 and prt_balance in t001 to a float or currency type).

5. To satisfy your chalenge (have a total without sum a big table) I've created a field prt_balance in t001. Update this balance when a new transaction occours. Trackback the commutings without extra normalization is easy if you implement the suggestion in 3.

Feel free to comment, ask or sugest in this specs.

I hope I've helped. And buy me a lunch! ;)

Answer by Neville K for Suggested DB Schema for a Lunchtime Reimbursement Application

Interesting problem - let's make some assumptions.

One person may buy lunch for one or more others (at least for the purpose of this app - if they buy themselves lunch, we don't care).

All lunches have an equal value - we're assuming the unit of measurement is "lunch", not "money". So, you buy me lunch at the Ritz, I buy you lunch at McDonald's and we're square.

The only way of paying your debt is through buying lunch. There's no cash way to get off the hook, and no way to "waive" the debt.

When you owe me 3 lunches, and then buy me one lunch, there's no direct relationship between the lunch you're buying me and any of the lunches you owe me - lunches a fungible.

If you want to retain history and stuff, I think you will need a transactions table of some type. I don't think summing those transactions will have any performance impact unless you're building a solution for millions of people - can you explain your dislike of this?

My proposal schema would be something like:

**User**  user_id pk  name    **lunch**  lunch_id pk  date    **transaction**  transaction_id pk  user_id  value  lunch_id  

Thus, when I buy you lunch, we create a new record in the "lunch" table, and insert a transaction with your user id, a value of "-1", and a reference to the lunch id. We also create a transaction for me, with a value of "+1".

We can see a user's balance at any time by joining the lunch table to the transaction table, and querying for all transactions where the lunch date between two dates.

We can see the current balance for a user by adding all their values.

Answer by NealB for Suggested DB Schema for a Lunchtime Reimbursement Application

Your suggested outline sounds good... Start with the following relations to track people and transactions:

 Person(*PID*, Name)    

Where PID is a surrogate key to uniquely identify each Person.

 Transaction(*TID*, Description, Date)    

Where TID is a surrogate key to uniquely identify each transaction you wish to track (eg. Lunch).

Track who owes who:

 Owes(*PIDOwes*, *PIDOwed*, *TID*, Amount)    

Where PIDOwes and PIDOwed are ID's from the Person relation. TID identifies the transaction responsible for creating the debit and its amount.

Each time a group goes to lunch a new Transaction is created and the Owes relation is updated to reflect who owes who as a result. This is what your database would look like after Jason buys David a 15.00 lunch; David buys Roslyn lunch for 10.00 and Phillip buys Roslyn lunch for 12.00. Assume the cost of lunch is split 50:50 (you can split it however you choose. The important bit is that debit is assigned to each person):

Person(J, Jason)  Person(D, David)  Person(R, Roslyn)  Person(P, Phillip)    Transaction(1, Lunch, 2011-03-04)  Transaction(2, Lunch, 2011-03-05)  Transaction(3, Lunch, 2011-03-06)    Owes(D, J, 1, 7.50)  Owes(R, D, 2, 5.00)  Owes(R, P, 3, 6.00)  

Commuting a debit:

When David commutes Roslyn's debit to Jason you would record the following transactions:

Transaction(4, CommuteDebit, 2011-03-07)    Owes(R, J, 4,  5.00)  Owes(R, D, 4, -5.00)  

It may be prudent to verify that Roslyn does in fact owe David at least 5.00 before accepting this transaction!

The second Owes row above could have been recorded as David owes Roslyn 5.00 but I like the +/- method since it captures the idea that this is a zero sum transaction. Another benefit is that you can identify who initiated the commuted debit because they will always be the PIDOwed in the Owes row having a negative amount.

The following transaction is created when Roslyn picks up the tab for Jason's portion of lunch costing 13.00 split 50:50:

Transaction(5, Lunch, 2011-03-08)    Owes(J, R, 5, 6.50)  

Suddenly things get complicated... Reciprocal debits accumulate. David owes Roslyn and Roslyn owed David. These debits should cancel each other out such that David owes Roslyn 1.50. The thing about the owes relation is that, with the exception of a debit commute, amounts only accumulate. Obtaining a balance is achieved by netting all 'A' owes 'B' from 'B' owes 'A'. The Owes relation is a record of transaction details, it does not provide running balances.

A query to calculate the balance for 'Jason owes Roslyn' is pretty simple and goes something like this:

select sum(case when PIDOwes = 'J' then                       +amount                  else                       -amount                  end)  from Owes  where PIDOwes in ('R', 'J')    and PIDOwed in ('J', 'R')  

This query will return 1.50 - the amount Jason now owes Roslyn.

To find who Roslyn should next treat to lunch is obtained by iterating the above query over the Person table replacing 'J' on each iteration (Roslyn remains constant). When the sum is positive, Roslyn owes. When the sum is negative, that person owes Roslyn.

Such a query will result in:

Phillip  6.00  Jason   -1.50  

So, Roslyn should treat Phillip and Jason should treat Roslyn.

Calculating the overall balance for an individual, David for instance, would be done by summing all the Owes rows having David as the PIDOwes and subrtacting them from the sum of rows where David is the PIDOwed.

The basic problem with this scheme is that you have to sum over the entire Owes relation in order to obtain balances. A possible demoralization for optimization could be to maintain a Balance relation such as:

 OwesBal(*PIDOwes*, *PIDOwed*, Balance)  

Each time a row is added to the Owes relation recording that A owes B some amount, the OwesBal relation is updated as follows:

set TranAmt to whatever A owes B according to the new Owes row.    select Balance as AowesB  from OwesBal  where PIDOwes = 'A'    and PIDOwed = 'B'    select Balance as BowesA  from OwesBal  where PIDOwes = 'B'    and PIDOwed = 'A'    if BowesA < TranAmt then     TranAmt = TranAmt - BowesA     BowesA = 0.00  else     BowesA = BowesA - TranAmt     TranAmt = 0.00    update OwesBal  set Balance = BowesA  where PIDOwes = 'B'    and PIDOwed = 'A'    update OwesBal  set Balance = Balance + TranAmt  where PIDOwes = 'A'    and PIDOwed = 'B'  

Of course you need to catch 'not-found' conditions and create initial OwesBal rows as new combinations of lunch partners occur.

The above accounting model does not easily allow for tracking total lunch expenditures over time. It just lets you keep track of who owes who. Tracking total expenditure could be implemented by adding the following relation to your model:

 Share(*PID*, *TID*, Amount)  

The Share relation would be populated at the time the Transaction and Owes relations are populated. Each person would enter in their share. For example:

Share(D, 1, 7.50)  Share(J, 1, 7.50)  Share(R, 2, 5.00)  Share(D, 2, 5.00)  Share(R, 3, 6.00)  Share(P, 3, 6.00)  Share(J, 5, 6.50)  Share(R, 5, 6.50)  

Transactions such as debit commuting would not be recored. You can obtain the cost by date by joining to the Transaction relation.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72