All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂