Translating Database Models to Models on Ruby On Rails

Question

I am beginning Ruby On Rails through a purchase/resale platform project at school. I'm having an issue with my models when I try to translate them from my relational model.

Firstly, I've modelled my database. Here is simplified the entity-relationship model :

I've then translated it in a relational model :

Finally, I've implemented it in Ruby On Rails.

• I've implemented a model Client :

  class Client < ApplicationRecord
      attr_accessor :name
  
      validates :name, :presence => true
  
      has_many :purchasings, :dependent => :destroy
  
      has_many :sellers, :through => :purchasings
      has_many :articles, :through => :purchasings
  end
  

• I've implemented a model Seller :

  class Seller < ApplicationRecord
      attr_accessor :name
  
      validates :name, :presence => true
  
      has_many :purchasings, :dependent => :destroy
  
      has_many :sellers, :through => :purchasings
      has_many :articles, :through => :purchasings
  end
  

• I've implemented a model Article

  class Article < ApplicationRecord
      attr_accessor :quantity
  
      validates :quantity, :presence => true
  
      has_one :purchasing, :dependent => :destroy
  
      has_one :client, :through => :purchasings
      has_one :seller, :through => :purchasings
  end
  

• I've implemented a model Purchasing :

  class Purchasing < ApplicationRecord
      attr_accessor :client_id, :seller_id, :article_id
  
      belongs_to :client, :class_name => "Client"
      belongs_to :seller, :class_name => "Seller"
      belongs_to :article, :class_name => "Article"
  
      validates :client_id, :presence => true
      validates :seller_id, :presence => true
      validates :article_id, :presence => true
  end
  

• I've modified the Purchasing database migration :

  class CreatePurchasing < ActiveRecord::Migration[5.1]
      def change
          [...]
  
          add_index :purchasings, :client_id
          add_index :purchasings, :seller_id
          add_index :purchasings, :article_id
          add_index :purchasings, [:client_id, :seller_id], :unique => true
      end
  
      def down
          [...]
      end
  end
  

I know this is incorrect because when I execute the following code on the Rails console :

cl1 = Client.create(:name => "John")
cl2 = Client.create(:name => "James")
sel1 = Seller.create(:nom => "Jack")
sel2 = Seller.create(:nom => "Jil")
a1 = Article.create(:quantity => 5)

p1 = Purchasing.new(:client => cl1, :client_id => cl1.id, :seller => sel1, :seller_id => sel1.id, :article => a1, :article_id => a1.id)
p1.save
p2 = Purchasing.new(:client => cl2, :client_id => cl2.id, :seller => sel1, :seller_id => sel1.id, :article => a1, :article_id => a1.id)
p2.save

p2.save returns true whereas an article can't be sold by a same seller and bought by two clients different.

Answer 1

You are adding the index on incorrect columns on purchasings table. As per the requirement, the article_id and seller_id should not get repeated ideally. So you actually need is a uniqueness constraint on seller_id, and article_id column. You can do so by creating an unique index on the composition of two columns seller_id, and article id on the database layer. You should also add the application layer validation on the purchasing model.

class Purchasing < ApplicationRecord
attr_accessor :client_id, :seller_id, :article_id

belongs_to :client, :class_name => "Client"
belongs_to :seller, :class_name => "Seller"
belongs_to :article, :class_name => "Article"

validates :client_id, :presence => true
validates :seller_id, :presence => true
validates :article_id, :presence => true

validates :article_id, uniqueness: {scope: :seller_id}
end

Now you should also write a database migration to add the unique index on these two columns.

    class AddUniquenessConstraintInPurshasing < ActiveRecord::Migration
       def change
         add_index :purchasings, [:article_id, :seller_id], :unique => true
    end

end