Question :
I have an payment entity that can take a form of bank transaction or cash. So, to achieve normalizing, I put cash payment primary key and transaction primary key in the payment entity. But, I doubt is the best approach, because payment can take just one form, and if the user made a mistake and want to change the payment method, I will have (myself as a developer) to make sure to delete the previous one (which is against normalizing ?!).
So i wonder if there is any better way to do this
Answer :
It’s hard to say the best approach, with the limited details provided. But, from what’s provided, I think it’s safe to say a transactions is a transaction, and a transaction is a transaction. It does not matter the form of the transaction.
As such, I assume transaction is going to be the “driving” table in your scenario. I.E. transactions will happen, and they’ll either be by cash or bank account, and mapped accordingly. With that said, there can always be additional tables to provide additional detail, based on transaction type.
So, in your transaction table, add a column for transaction_type
and remove bank
. If a transaction is cash, you just flag it as cash in the transaction_type
column, direclty in the transaction
table. If cash, you’d likely need no additional details.
If the transaction was by bank_account, flag it as bank_acct
. You could then have an intermediary table called transaction_bank_map
that contains transaction_id
and bank_account_no
to link the transaction to the bank account the payment came from. This way, you’re storing details of transactions once, and you’re storing details of bank accounts only once.
This would remove the need for the payment
and cash_payment
tables.
Or, skip the intermediary table
Additionally, you could skip the intermediary table, and leave the bank
column in the transaction
table, and only populate the column when tran_type = bank
. If most of your transaction will be by bank account (which is likely the case for most businesses the days), this may be more appropriate.