Suppose there is a set of items, a set of stores, and a set of quantity values. Each item is located in at least one store and each store has at least one item. For each store, each item has a particular quantity. The quantity associated with each item depends upon the store. For example, there is an apple and an orange (the items). The possible quantities are 10 and 20. The stores are Store A and Store B. An acceptable state of affairs according to this is: Store A contains 10 apples and 10 oranges. Store B contains 10 apples and 20 oranges. How should my database be structured to allow users to assign items to stores AND set their quantities based on their store location?
I have 3 models:
class Item < ActiveRecord::Base has_and_belongs_to_many :quantities has_and_belongs_to_many :stores end
class Store < ActiveRecord::Base has_and_belongs_to_many :items end
class Quantity < ActiveRecord::Base has_and_belongs_to_many :items end
I have the following tables: items, quantities, stores, items_quantities, and items_stores. The last two are cross-reference tables, named according to convention.
Given what I have so far, a user can associate items to stores and quantities to items, but users cannot associate quantities to items according to the store the items belong to. So going along with the example above, a user can place the apple item in Store A and assign the quantity 10 to apples, but there is nothing that specifies that Store A contains 10 apples. Any ideas?