Considering a trading system as an example. Would it make sense to have two different tables for credit transactions and asset transactions?
creditTrans : table id userId transType status amount timestamp assetTrans : table id assetId ** fk to asset table userId transType status amount timestamp
There are different types of transaction: Purchase, Sale, Deposit, Withdrawal
**Deposit and withdrawal are for fiat currency like dollar and buying/selling assets
In Purchase & Sale Transactions, both tables will have data but in Deposit & Withdrawal only creditTrans data will be inserted.
I'm actually in doubt for over a month whether to merge this two table into one (and have a nullable foreign key (assetId) for deposit/withdrawal transaction) or keep a separate table for different kinds (avoid nullable - have to insert multiple table in purchase/sale transactions).
This is the related database schema that I've come up with so far.