Mysql – associate different credit cards with every purchase

database-designMySQLschema

I have a schema in which I store data regarding customer's credit cards and I want to find a way to associate each order (can have multiple payment entries -partial payments-) with its the selected credit card in the checkout funnel

So far I have

Order
-----
id
status (started, completed)


customer_cards
---- 
id
customer_id
card_id (entity card_token_id)
other card_data


Payment_entries
-----
id
payment_token (charge_id from entity, like stripe)
order_id (FK)
payment_type_id (FK to payment_type) (in this case id for stripe entry in payment_type)
status

I would like to know which customer_cards has been used to place an order (first order-payment_entry entry)

I've been thinking on adding a new column in Payment_entries that stores the card_id or customer_cards id of each payment entry but I'm worried It would not scale if we add a different payment_type

Other idea is to add a table with the relation order_id – customer_cards but it sounds a bit redundant to me.

What do you folks advise?

Thanks

Best Answer

I think it's fine to do your first idea with adding the id of customer_cards as a field to the Payment_entries table. This then makes it a linking table between Order and customer_cards so you can answer the question "which customer cards have been used to place an order?".

For scalability, I would advise calling the new column something like payment_method_id in Payment_entries that way it's generically re-usable for other payment_types you may add as you scale.

Related Question