Duplicating data in database - web application project

Question

Me and 2 friends are starting a project, simple social web app. The problem, that's troubling me is that we have a table Wallet, which will store sum of all transaction values between two users.

TABLE WALLET:
ID,
UserID,
FriendID,
TotalDebtAmount

Based on experiences from previous version, we decided to duplicate wallet entries, so if in total User1 owes User2 200$, Wallet will have 2 entries:

ID  :  UserID  :  FriendID  :  TotalDebtAmount
1      User1      User2           -200
2      User2      USer1            200

This will be helpful when creating a list of all user debts and credits, because we can just select all entries, WHERE UserID=user. If amount is positive, our friend owes us, if it is negative, we owe our friend.

Previous concept looked like this:

TABLE WALLET:
ID,
CreditorUserID,
DebtorUserID,
TotalDebtAmount

and User1 owing User2 200$ would generate one entry:

ID  :  CreditorUserID  :  DebtorUserID  :  TotalDebtAmount
1      User1              User2               -200

but selecting all credits and debts would require WHERE CreditorUserId=user OR DebtorUserID=user and when displaying it we would have to check in which column current user is listed and modify amount accordingly (positive amount means that DebtorUser owes to CreditorUser and negative - the opposite, so when displaying debts/credits on website for User2, we need to change -200$ to 200$ as User2 is in DebtorUserID column).

We find the duplicate data solution much easier, but this project is primarily for educational purposes so our priority is to use good programming practices, and some people say that duplicating data isn't one. What do you think?

In relational modeling, *duplicating data* doesn't mean having identical values. It means having identical values *with identical meanings*. — Mike Sherrill 'Cat Recall', Jan 08 '13 at 14:34

score 0 · Answer 1 · answered Jan 08 '13 at 16:23

0

You duplicate data, which is unnecessary. You can do your query with the first type of table-layout also. The query would be a bit more complex of course, but it is not worth duplicating the data in my opinion.

answered Jan 08 '13 at 16:23

Argeman

1,345
8
22

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

0

Your approach of writing two records, a credit and a debit, for a transaction is a good one. This forms the basis of double entry bookkeeping, which has been used in accountancy for hundreds of years. As you've noted in your question, this will make many operations and queries easier to handle.

For a fully worked example of it, see this post

edited Apr 13 '17 at 12:42

Community

1
1

answered Jan 08 '13 at 16:55

Chris Saxon

9,105
1
26
42

Duplicating data in database - web application project

2 Answers2