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?