DB Relationships

Question

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?

Answer 1

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.