0

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).

Additional details

This is the related database schema that I've come up with so far.

enter image description here

  • If the null bothers you, you could make a joining table with just transid and assetid columns. This would also allow for a 0..n relationship not just a 0..1 relationship. – Hogan Jan 4 at 20:57
  • @Hogan thanks for the response. I've updated the question with my currency used schema. I'm a little bit confused with your suggestion though, I'm not sure I fully understand it.is it like having an assetTrans table with transId (creditTransId- creditTrans : table) and assetId (and maybe assetAmount) as n:n table? – Mehrdad Kamali Jan 5 at 0:19
  • Yes that is what I meant – Hogan Jan 6 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.