mysqldatabasemysql-workbenchentity-relationshipdb-schema

Integrating payment methods in db schemas


I have a requirement where I need to create A database where a user can have multiple payment methods and against those multiple payment methods multiple transactions can be processed.

I have created the following schema

enter image description here

WHY THESE TABLES:

user: This table contains information about the user. Ex: First Name, Last Name, Email etc

user_payment_method: Since a single user can have multiple payment methods I created a table to identify all the payment methods he has so that i can reference them in the transactions table and could know on exactly which payment method the transaction was made on.

transaction: This table contains all the data about all the transactions. Ex: Time, user_id, user_method_id, amount etc

payment_method: This table acts as a junction table(pivot table) to reference all the payment methods that could exist. Since all payment methods have different details I cannot make a single table for this.

specific payment method tables: Tables like bank_transfer and paypal contain the specific details the user has about that payment method. Ex: paypal keys or bank account numbers

THE PROBLEM

I am stuck at creating a relationship between payment_method and specific payment method tables.

How do I reference different payment methods within a single column in the payment_method table. Do i create a junction(pivot) table for each specific payment method?

EDIT: If anyone has a simpler different approach please let me know too I am open to all ideas.


Solution

  • The problem can be categorised as modelling inheritance. You have n payment methods, each with different (user specific) properties. The simplest is TPH table per hierarchy: put all the user properties for all payment methods on the user_payment_method table. There are other options covered here. Forget about pivot table: you're modelling a DB schema and you only need tables and columns. Think about what you need to store, how you need to retrieve it, and the importance of storing each fact once only.