Myujth

I am trying to work out the best DB structure to power this setup:

I have two types of user: sales and market.
Each user can have a number of clients.
Each client can have a number of transactions.Transactions will be assigned a transaction type: 'sales' or 'market'. Each client can have only one sales user and one market user.

I need to be able to retrieve a list of transactions for each user, so for a 'sales' user, user.transactions returns all transactions with a 'sales' type for all clients where the client's 'sales' user is that user

I have started out with the following setup but can't get all the queries I need, for example:

u = User.find(5)
Transaction.all.where(sales_ee: u)
# This is returning nothing

This is the setup so far:

class User < ApplicationRecord
 has_many :sales_clients, :class_name => "Client", :foreign_key => "sales_ee_id"
 has_many :market_traders, :class_name => "Client", :foreign_key => "market_ee_id"
 has_many :clients, dependent: :destroy
 has_many :transactions, :through => :traders
end

class Client < ApplicationRecord
 belongs_to :sales_ee, :class_name => 'User', optional: true
 belongs_to :market_ee, :class_name => 'User', optional: true
 has_many :transactions, dependent: :destroy
 belongs_to :user
end

class Transaction < ApplicationRecord
 belongs_to :client
 has_one :user, :through => :client
 has_one :sales_ee, :through => :client
 has_one :market_ee, :through => :client, required: false
end

I'm not sure if having two classes of user may be a more appropriate setup - any help would be much appreciated.

Changes

 class Client < ApplicationRecord
- belongs_to :sales_ee, :class_name => 'User', optional: true
+ belongs_to :sales_ee, :class_name => 'User', :foreign_key => "sales_ee_id", optional: true
- belongs_to :market_ee, :class_name => 'User', optional: true
+ belongs_to :market_ee, :class_name => 'User', :foreign_key => "market_ee_id", optional: true
 has_many :transactions, dependent: :destroy
 belongs_to :user
 end