Database reference multiple entity types from one table

Question

So, I am creating a transaction database table to store, well, transactions between the merchant and customer.

Sometimes the customer will pay with a store card and other times with a credit card (cash is not used in this system).

How do I model this in the database. My current thinking is that the transaction table has a field for store card transaction and one for credit card transaction. These fields will then reference two seperate tables to store data specific to the payment method.

This means that one of these fields will always be null while the other is a reference to the appropriate payment method table.

Example: A transaction was completed with a credit card and not a store card. This means that in the transaction table, the store card field is null, while the credit card field contains a reference to the credit card transaction table. This credit card transaction table stores data related to a credit card transaction e.g. auth code, transaction id etc.

The reason this has me confused is because I was taught in college that NULL values are bad. But is this not a case where they are required.

Is there a term for this type of problem or what query should I be using to match it in Google?

Many thanks, Conor.

What they should have taught you in college is that overly simplistic rules of thumb are bad. Null are not bad. 0NF is bad. 2NF is almost always bad. Sparsely populated tables are very likely to be bad. There are lots of reasons why you find nulls in a table. They are perfectly legitimate if you are using them appropriately. — Joel Brown
– Joel Brown, Commented Mar 18, 2012 at 11:30

Michael Riley - AKA Gunny · Accepted Answer · 2012-03-17 13:20:45Z

1

Do not use two card number fields

CardNumber should be one column and contain any card number from any transaction. You should have another table somewhere on the system that keeps track of which "Store Cards" have been issued. A join from the transaction table to the "Store Card" table will identify which transactions were made using store cards and which ones were made using credit cards.

answered Mar 17, 2012 at 13:20

Michael Riley - AKA Gunny

5,2144 gold badges48 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jim Over a year ago

Hi Gunny, that is a good proposal i'll go with that. What about storing data related to a cc transaction and not store card transaction. i.e. authorisation code. Where shall I store that? transaction table, with null for store card?

Michael Riley - AKA Gunny Over a year ago

Connor, I think you should store all transactions in one table. Software used by banks rely on transaction codes for everything. There are separate transaction codes for all kinds of transactions... to many to mention here.

Collectives™ on Stack Overflow

Database reference multiple entity types from one table

1 Answer 1

Do not use two card number fields

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Do not use two card number fields

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related