Are multiple nullable foreign keys a bad design?

user_4493817 0
Viewed 59

We have a table to store user financial transactions.

A transaction can be incremental or decremental, we have 3 types of transactions, increase with a payment, increase with receiving of a gift, and decrease with purchase of product, So our transaction table contains 3 foreign keys:

[PaymentId]   [GiftId]   [RequestId]

Is this a bad design? What better alternative is there?

I think it is complicated to join [Transaction] table with 3 other tables to get details of each transaction to display list of user transactions

entity-framework-core sql-server sql
1 Answers

It seems like you'd be better off with a column called something along the lines of TransactionType and then just storing the TransactionTypeId in a single column as well. Using these two columns you can represent any number of types of Transactions. You actually find this common in the financial data world with dimension tables that represent things like Cost Type, etc.

user_50590850
Related