1

I am a fresh grad with very little database design experience.

Imagine an online gambling database. I am modeling using double-entry bookkeeping. Users can place Wagers, which payout wins, losses, or potentially be canceled. Wagers are essentially pending Transactions. After confirming the result of a Wager, we add rows in a Transactions table, rows of types Credit and Debit.

However, I would like to include rows of types Deposit and Withdrawal in the transactions table too.

Rows in Transaction Table related to Wagers will need foreign keys for the Wagers.

Rows in Transaction Table related to Deposits/Withdrawals will need foreign keys for Payment Processor information, potentially refund information as well.

I think that a singular transaction table that maintains account balance changes would be good, so I don't want to split up Credit/Debits from Deposit/Withdrawls.

In a situation like this, is it acceptable to design my Transactions table knowing there will be 3-5ish empty columns in every row? Or would you do a different solution?

Thanks you for your advice.

1
  • I'd use multiple tables and create a VIEW (using JOIN) that does what you want. Commented Jun 6, 2022 at 17:09

1 Answer 1

0

Having everything in a single table gets quite clumsy. Instead have 2 main table.

  • One with the individual changes to each account -- deposit / withdrawal.
  • One with account balances.

Neither is a "transaction". A Transaction is a combination of INSERTing a row to the first able and UPDATEing a row in the second table. When talking about databases, please use the term "transaction" only for the specialised meaning it has in that context.

The second table can be derived from the first, but that is less practical then being sure to keep them in sync. And BEGIN...COMMIT (that surrounds the SQL in a database transaction) helps with that.

FOREIGN KEYs are primarily a data-integrity check. Your wording makes them sound more important than that. Forget about FKs (for now) and focus on what Inserts/Update/etc need to be performed.

Uncompleted wagers probably need to be kept in a third table. And perhaps the wager row is deleted (or marked as consummated) when the betting is finished.

Again, the changes to the wagers table needs to be part of the database transaction when you make the other changes mentioned.

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.