Schema does not list all fields, but only the relevant ones

  1. User adds credits/funds to his account using paypal or Internet Banking (which has to be verified manually, and approved, so the 'status' field in the receipts' table)
  2. After a receipt has been approved, the amount will be added to the account_balance of the user
  3. When a user makes a purchase from the site, the amount will be deducted from his account_balance
  4. Also, a user can request a withdrawal of his funds

Table: users

id | name | email | password | account_balance | created_at

Table: receipts

id | user_id | payment_method | transaction_datails | amount | status | created_at

Table: purchases

id | user_id | product_id | amount | created_at

Table: withdrawals

id | user_id | amount | created_at


Is it better to have a transactions table that stores all receipts, withdrawals, purchases in one table?

  • You really should research how double-entry bookkeeping is done with a Cash Receipts Journal, Sales Journal, and Accounts Receivable Subledger. This technique invented by the Mafia 800 years ago is robust and highly parallel, hence scalable. Aug 31, 2014 at 3:02

1 Answer 1



You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else.

Withdrawals should be in a single table, and should have its own details as necessary ( paypal out-payment, bank out-payment etc, if applicable in future). Because withdrawals can be more than one - a guy who has $100 in his account can withdraw 4 $25 withdrawals in differing times. Which will have to be paid out in its own payment method and confirmed in its own fashion. Therefore withdrawals is a different identity.

And all the searches involving these entities will happen in that fashion too - you cant search a withdrawal IN RELATION TO two different purchases in Purchases. Or vice verse. They are independent entities which are only linked by user_id, and very loosely by account balance - the latter only as a limiting factor for the other.

Userts table is solid as it is and needs no change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.