0

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.

Fahad Sohail
  • 1,598
  • 4
  • 15
  • 32

2 Answers2

3

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 …

Manngo
  • 8,349
  • 6
  • 50
  • 74
2

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.

Community
  • 1
  • 1
bbsimonbb
  • 20,571
  • 9
  • 59
  • 92
  • So are you saying for each record in the payment methods table some of the rows will be left empty. Like if the user_payment_method is for paypal the bank details will be left empty and there will be a column which tells which type of payment method it is. Okay so if in the future if the user decides to add one more payment method. Like if he has bank transfer and he wants to add paypal too will a new row be created for that ? – Fahad Sohail Apr 13 '17 at 12:32
  • @FahadSohail In a database there are no empty rows. I think you mean columns? If so, then correct, some rows will have missing details, depending on the type of data for each payment type. That’s not a perfect solution, but it’s a relatively simple one. A better solution would be additional tables in a one-one relationship, but that takes more understanding, and can be worked on later. – Manngo Apr 13 '17 at 21:58
  • Yup that's it. Empty cells in a table are not a problem, and are very common in practice. The solution Manngo suggests is TPCC table-per-concrete-class. Once you know the name of the problem you're dealing with, you can quickly find [tons of stuff](http://blog.devart.com/table-per-type-vs-table-per-hierarchy-inheritance.html) on advantages and disadvantages. – bbsimonbb Apr 14 '17 at 07:13
  • The problem is this is a payment mechanism and I want to lock it as tightly as possible maintaining data integrity from all angles. Leaving empty rows isnt a data integrity issue? – Fahad Sohail Apr 14 '17 at 11:09
  • There's no such thing as an empty row in a DB. Are you sure you're paying attention? None of the options we're discussing here affect data integrity. Empty columns are certainly not a data-integrity issue. I wrote and maintained a payment gateway for 7 years. As you'll quickly read if you google the subject, a disadvantage of TPH is that you need to add columns to an existing table when/if you add a new payment method. This is not a huge deal. – bbsimonbb Apr 14 '17 at 12:19
  • I'm sorry i mean columns instead of rows. The link you provided also mentioned that TPH violates data integrity in point 2 in TPH disadvantages. Im just new to databases so im a little confused im sorry if my replies seems to be misleading – Fahad Sohail Apr 17 '17 at 06:07