0

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

1 Answer 1

0

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.

1
  • 1
    Thanks a lot J.D. just what I thought. Thanks also for the recommendation, I marked your answer as accepted.
    – soni
    Jan 11, 2021 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.