-1

I would like to have relationships:

  • users
  • accounts
  • cards
  • banks
  • credit_processors.

The question is how to design the model in the db. I got into a confusion when these scenarios came across:

  1. A card can be issued by a bank and the bank would link the card to a credit processor for instance, Chase would use Visa on their cards, Bank of America would use Mastercard and Visa on their cards.
  2. A card can be issued by a credit processor WITHOUT a bank for instance, Visa can issue their own cards, American Express can issue their own cards.

When the relationships in the db would be like this:

  • an account belongs to a bank and can have a credit processor through the bank

  • an account belongs to a credit processor directly without a bank.

Here is the link to my db diagram. Is there a better way to design this db diagram?

Moshe Levi
  • 19
  • 3

1 Answers1

0

Looks pretty good, but I don't think you need the 'resources' table since you can store the bank_id and the credit_processor_id in the 'cards' table

Just to walk through it from the top down

  • A User has_many Accounts
  • An Account has_one Card (can an Account have more than one card)?
  • A Card should belong_to an Account, Bank (optional field), CreditProcessor, Category and possibly User if you think you'll want to see all of a Users Cards pretty often

You may also want to do a many-to-many relationship between Banks and CreditProcessors depending on how often you think you'll query that.

SomeSchmo
  • 665
  • 3
  • 18
  • Thank you @SomeSchmo for your time. The idea of resources table to make the has-many-to-many relationship between the banks and credit_processors this is exactly where I struggled. How would you design the many-to-many (banks, credit_processors) relationships? – Moshe Levi Jul 11 '17 at 19:38
  • I see. That is tricky, I can see why you wanted to have the Card point to that join table which would then reference the Bank and CreditProcessor, but since you can have Cards with no Bank that doesn't quite work. Because of that detail I would keep the join table simple, just the Banks and the CreditProcessors they associate with, and have the Cards themselves store the ids of the Banks and CreditProcessors they belong_to – SomeSchmo Jul 11 '17 at 19:46
  • What if I want to list all credit_processors that belongs to a bank or vis versa ? Or list all cards that under bank or credit_processor? – Moshe Levi Jul 11 '17 at 19:54
  • As long as you setup the database tables and Ruby models correctly ActiveRecord should take care of most of that for you. Have you done stuff like join tables in rails before? – SomeSchmo Jul 11 '17 at 20:13
  • Ruby models would know how to list Banks and Credit_processors with their relationships without the Resources table? – Moshe Levi Jul 11 '17 at 20:23
  • No you would need a join table between those two that would store the bank_id and the credit_processor_id and you'd have to define a 'has_many through' relationship in the bank and credit_processor models – SomeSchmo Jul 11 '17 at 20:32