Designing multiple types of payment schema in SQL?

Question

I've payment system where I can do two types of payment for an order.

1. Online - Mobile payment
2. Offline - Cash/ Cheque

I've credited 4 tables, I'm outlining only important columns not everything for simplicity.

List of table Names

Payment
CashPaymentDetails
ChequePaymentDetails
MobilePaymentDetails

Schema Details

CashPaymentDetails
cash_id int
cash_amount decimal(18,2)

ChequePaymentDetails
cheque_id int
cheque_amount decimal(18,2)
cheque_type

MobilePaymentDetails
mobile_id int
mobile_amount decimal(18,2)

Payment
payment_id int
payment_amount  decimal(18,2)
fk_cash_id int (foreign key reference to CashPaymentDetails table)
fk_cheque_id (foreign key reference to ChequePaymentDetails table)
fk_mobile_id (foreign key reference to MoblePaymentDetails table)

User can do either cash/cheque/mobile, only one payment mode, this makes my two foreign keys NULL which i feel is overhead because if going forward I've 10 different types of payment method, 9 Fk references will be NULL.

Payment

Payment_id payment_amount fk_cash_id fk_cheque_id fk_mobile_id
1 300 1 NULL NULL
2 200 NULL 1 NULL
3 400 NULL NULL 1

what should be an optimal design to create this schema?

Answer 1

Create table for PaymentMethod and refer its PK in Payments table then according to payment method you can make appropriate entry in respective tables of payment method. I have also added Payment_id in [CashPaymentDetails], [ChequePaymentDetails] & [MobilePaymentDetails] tables.

Schema Details

[PaymentMethods]
Payment_Method_id int
Payment_Method_Description varchar(50)  --  ("cash", "cheque", "mobile" etc)

[Payment]
Payment_id int
Payment_Method_id int
payment_amount  decimal(18,2)


[CashPaymentDetails]
cash_id int
payment_id int
cash_amount decimal(18,2)

[ChequePaymentDetails]
cheque_id int
payment_id int
cheque_amount decimal(18,2)
cheque_type

[MobilePaymentDetails]
mobile_id int
payment_id int
mobile_amount decimal(18,2)