how to model an entity with two possibilities (sql)

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 model an entity with two possibilities (sql)

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.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply