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
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.
Answer
I would probably simplify the schema as follows:
user <- transaction -> payment method
The payment method would include your PayPal and Bank Transfer, which should not be different tables.
Generally, you should think of your payment method as a type of transaction.
When constructing a database, you look for the tables where the real action is. In this case it’s the transaction table. You can recognise it from an entity diagram as the one with the foreign keys pointing outward. In this case, you can say that a transaction belongs to a user and is of a certain type.
The transaction table would have the actual payment details, such as the date, amount, transaction number etc.
You could also have a table of preferred payment details. That would give you something like this:
user <- transaction -> payment method
<- preferred ->
Remember, that preferences can change, so the data from the preferred table should be copied into the transaction table, to allow the preferences to change later.
Needless to say, we presume that you are taking all the proper precautions regarding passwords, account details and other sensitive data …