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
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.
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.