Mysql – Need help with data model

database-designMySQLschema

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?

Best Answer

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.

Related Question