Topic: Saving Data in a Serialized Hash vs Relationship

Hi Everyone,

I have a pretty basic question and I hope someone can help me out with the theory/modeling.

Our database has inventory items, some of which can be bought from various suppliers.  I can setup the relationships but the problem I'm running into is how to model and store the pricing.  Each vendor gives different pricing and different quantity breakdowns.

Example:

AA Battery
- Supplier 1
10 units or less - $3.43 per unit
50 units or less - $3.20 per unit
- Supplier 2
20 units or less - $3.33 per unit
90 units or less - $3.10 per unit

I would like to be able to save all this information but am not sure if it's best in a serialized hash directly on the products_suppliers table or as another relationship.  I would like a method that will be able to put in an item and quantity and pull the best price, so that's the end goal.

Any advice?

Re: Saving Data in a Serialized Hash vs Relationship

definitely use another table and relationships


use has_many :through,

i.e.

class Item < ActiveRecord::Base
  has_many :price_tiers
  has_many :vendors, :through => :price_tiers
end
 
class PriceTier < ActiveRecord::Base
  belongs_to :items
  belongs_to :vendors
end
 
class Vendor < ActiveRecord::Base
  has_many :price_tiers
  has_many :items, :through => :price_tiers
end

You'll end up with an intermediate join table, price_tiers, and you can add attributes to that join table, i,e:

class CreatePriceTiers< ActiveRecord::Migration
    create_table :price_tiers do |t|
      t.integer :vendor_id
      t.integer :item_id
      t.integer :tier_start
      t.integer :tier_end
      t.float :price
    end
  end
end
Joe got a job, on the day shift, at the Utility Muffin Research Kitchen, arrogantly twisting the sterile canvas snout of a fully charged icing anointment utensil.