CODEWITHSUNDEEP
mysqlsqldatabasepostgresqlrdbms
Macks
Macks

Reputation: 1776

Database design: Associating tables of variable types

I am looking for a solution to (something I imagine to be) a common and trivial problem, but I couldn't find the correct words to find solutions on Google.

Starting situation

I have a table orders that is associated to a product and a customer:

orders (id, product_id, customer_id)

The problem

Each order must have a payment associated. These payments come from different payment processors (e.g. Paypal, stripe, Google Wallet, Amazon Payments) and thus have different types of data and data fields associated to them.

I'm looking to find a clean and normalized database design for this.

My own attempt/idea

I could create separate tables for the different types of payments and associate the order from there:

paypal_payment (id, order_id, currency, amount, [custom paypal fields])
stripe_payment (id, order_id, currency, amount, [custom stripe info])
direct_debit_payment (id, order_id, currency, amount, [custom direct debit info])

The problem: With this approach I would need to SELECT from each table for every payment type to find an associated payment to an order, so that doesn't seem very efficient to me.

What is a clean solution to this problem? I'm using MySQL if that is relevant at all.

Thanks for your help! :)

Upvotes: 0

Views: 396

Answers (3)

Kris
Kris

Reputation: 14458

Your Payment table should have all fields that are common to all payments (amount, type of payment, etc) as well as a unique ID.

Variable fields would then be stored in a second table with three columns:

  • Payment UID (foreign key to the Payment table)
  • Type (what kind of data this current row is storing, i.e. the name of a custom field for the payment type)
  • Value

This allows you to associate any arbitrary number of custom fields with each payment record.

Obviously, each payment could have any number of entries in this secondary table (or none if none are needed).

This works quite well as most of the time you wont need the payment type specific info and can do queries that ignore this table. But the data will still be there when you need it.

Upvotes: 3

Adrian Nasui
Adrian Nasui

Reputation: 1095

A normalized way you could do this is by having a base payment table and extension tables for the other payment types. All common payment information would go in your payment_base table. 

payment_base(payment_id, order_id, currency, amount)
paypal_payment (paypal_payment_id, payment_id, [custom paypal fields])
stripe_payment  (stripe_payment_id, payment_id, [custom stripe info])
direct_debit_payment  (direct_debit_payment_id, payment_id, [custom direct debit info])

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

If you're never going to be using those fields' contents in a where or join clause (which it usually is):

  1. Add a payment method field (an enum or varchar)
  2. Serialize the paypal, stripe, or whatever the client used as json
  3. Store the thing using the most appropriate database type -- text in MySQL, json in Postgres.

Upvotes: 1

Related Questions