How to model a customer - merchant transactions model conceptually, like in ActiveRecord for instance?

Question

I have a customer model, a merchant model and a transactions model. Customers can make transactions to merchants and merchants can make transactions to customers as well. Eventually, I'd like to query things like:

1. All transactions made by a customer
2. All transactions made by a merchant
3. All of customer X's transactions to merchant Y (and vice versa)
4. Optional: final all transactions with a certain tag first, and then find all associated merchants and customers related to those transactions. This is a nice-to-have feature, but if it's too complicated to explain in an answer then don't worry about it

So conceptually how should I create associations for each of these models? Like Customers has_many :merchants, through: :transactions (and vice versa)? or use polymorphic associations? etc etc.

Thanks a lot for your help!!!

Answer 1

OK, the suggestion by @infused looks great. Here is the same in ActiveJDBC

First define models:

public class Customer extends Model {}
public class Merchant extends Model {}
public class Transaction extends Model {}

Second, create tables:

CUSTOMERS:

id | first_name | last_name | etc

MERCHANTS:

id | name | address1 | etc

TRANSACTIONS:

id | customer_id | merchant_id | tag | etc

find all of a customer's transactions:

customer.getAll(Transaction.class);

To find all of a merchant's transactions:

merchant.getAll(Transaction.class);

Find all of Customer #1's transactions for Merchant #25

customer = Customer.findById(1);
customer.get(Transaction.class, "merchant_id = ?", 25);

Find all of Merchant #1's transactions for Customer #8

 merchant = Merchant.findById(1);
 merchant.get(Transaction.class, "customer_id = ?", 8);

Find all Transactions by a tag (assuming tag is a string field):

transactions = Transaction.where("tag = ?", "best-seller");

Find all Merchants with Transactions tagged with 'best-seller':

transactions = Transaction.where("tag = 'best-seller'").include(Merchant.class);
//iterate over transactions, and get a merchants:
transactions.get(i).getAll(Merchant.class)

This approach will run only 2 SQL queries, and is very fast.

I hope this helps

Answer 2

Conceptually, the Transaction model joins Customer and Merchant, so:

class Customer < ActiveRecord::Base
  has_many :transations
  has_many :merchants, through: :transactions
end

class Merchant < ActiveRecord::Base
  has_many :transactions
  has_many :customers, through: :transactions
end

class Transaction < ActiveRecord::Base
  belongs_to :customer
  belongs_to :merchant
end

To find all of a customer's transactions:

customer.transactions

To find all of a merchant's transactions:

merchant.transactions

Find all of Customer #1's transactions for Merchant #25

customer = Customer.find(1)
customer.transactions.where(merchant_id: 25)

Find all of Merchant #1's transactions for Customer #8

merchant = Merchant.find(1)
merchant.transactions.where(customer_id: 8)

Find all Transactions by a tag (assuming tag is a string field):

transactions = Transaction.where(tag: 'best-seller')

Find all Merchants with Transactions tagged with 'best-seller':

merchants = Merchant.includes(:transaction).where(transaction: {tag: 'best-seller'})

Find all Customer with Transations tagged with 'best-seller':

customers = Customer.includes(:transation).where(transaction: {tag: 'best-seller'})