Using models and migrations in code

Question

I try to wrap my head around a problem I try to tackle in a Ruby on Rails application, but after three days of searching and trying, I seem to get tunnel vision and am stuck:

I have products and shops and a product can be sold by many shops. The prices of that product can differ per shop and I want to create a history of the prices per shop, so I want to save the price information in a separate table.

I created the following migrations:

class CreateProducts < ActiveRecord::Migration[5.2]
  def change

    create_table :products do |t|
      t.string :name
      t.text :description
      t.string :ean
      t.text :category
      t.belongs_to :shop, index: true
      t.belongs_to :lists, index: true
      t.timestamps
    end

    create_table :shops do |t|
      t.string :name
      t.string :url
      t.integer :priority
      t.timestamps
    end

    create_table :products_shops do |t|
      t.belongs_to :products, index: true
      t.belongs_to :shops, index: true
      t.float :price
      t.timestamps
    end

  end
end

And the following Models:

class Product < ApplicationRecord
  belongs_to :shops
end

class Shop < ApplicationRecord
  has_many :products
end

My question: How do can I save the price information to the products_shops table? And how do I retrieve the data back with the product, so that I get the product information along with all shops having that product with the most recent price per shop?

Have you considered denormalizing your data model? Put it all in one table and create another table with the exact same fields to store the history. This may also help further down the road if/when your app gets a lot of traffic. — Nick M, Jul 28 '18 at 09:49
I will add a lowest proce field to the product table to prevent having to get the lowest price every product view. On the product detail page I will show the prices for all the shops carrying the product. Great suggestion! Thanks — Art Fisher, Jul 28 '18 at 11:50

VAD · Accepted Answer · 2018-07-28T09:03:46.950

I'm afraid your current products_shops table won't be very useful if you need to store price history to be able to get last price or something like that

You could create a separate Price model and prices table containing let's say product_id, shop_id and actual price. The model will look like

class Price < ApplicationRecord
  belongs_to :product
  belongs_to :shop
end

It may be useful to add has_many :prices associations to products and shops:

class Shop < ApplicationRecord
  has_many :products
  has_many :prices
end

class Product < ApplicationRecord
  belongs_to :shops
  has_many :prices
end

Then you'll be able to save multiple prices for every pair of shop and product, get all prices for every product and so on

For example, get all the prices for the product in the particular shop (i.e. price history for the product in the shop):

Price.where(product_id: your_product_id, shop_id: your_shop_id)

Price.where(product_id: your_product_id, shop_id: your_shop_id).order(:created_at).last will give the last price for the product in the shop.

Using models and migrations in code

1 Answers1