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
class Store < ActiveRecord::Base
class Quantity < ActiveRecord::Base
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.