Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Question:

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

share|improve this question
    
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. –  Pieter Geerkens Aug 31 at 3:02

1 Answer 1

No.

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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