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
status (started, completed)
card_id (entity card_token_id)
payment_token (charge_id from entity, like stripe)
payment_type_id (FK to payment_type) (in this case id for stripe entry in payment_type)
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?