how to model an entity with two possibilities (sql)

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

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.