SQL database design: many to many relationship between ‘accounts’ and ‘transactions’ tables such that a transaction can have ‘from’ and ‘to’

I’m trying to create a many-to-many relationship between two tables: ‘accounts’ and ‘transactions’, but with a catch. Every transaction must be related to two accounts, but each of those accounts must be specified as either the sender or receiver.

Likewise, each account should relate to all the transactions they are involved in. Is there a best practice database design pattern that solves this problem? I’m using Sequelize with Postgres if that’s helpful.

eg:

Accounts

id | name | transactions_sent (Transactions.id) | transactions_received (Transactions.id)

Transactions

id | notes | sender (Accounts.id) | recipient (Accounts.id)

Answer

I would simplify your design by creating an additional AccountsTransactions table with composite key from Accounts and Transactions tables and add a new column send_receive_indicator or transaction_type (S – Send/R – Receive) on Transactions table (assuming that each transaction is either send or receive type of transaction). This way you can control your many-to-many relationship on that AccountsTransactions by introducing that new transaction_type column in your query.