Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008

    Database Design help for stores

    Hello guys, I need some help hope you all can help me with this.
    So i have 12 little (I don't know what to call them but let's say) stores which are selling the same products (at this time 12). Like hot dogs, cola, gum and other stuff. Every day I am get a slip of paper from each store containing information what and how many products they sold, also how many they got supplied. I attached an image of how it looks. So I have 12 of those every day. So every store is like a little stock too, and besides that I have a big one which delivers to them what they need. I want to make a database which would keep track of:
    1. How much of each product was sold, every day and for each store.
    2. How much of each product was supplied to them, every day.
    3. And of course the revenues and all that.

    I was thinking of making some tables like this:
    - id
    - name
    - price
    - id
    - id
    - product_id
    - store_id
    - quantity
    - date
    but I not sure this is the best way, this doesn't cover the products they receive and what should I do about the totals of sales, calculate or store the somewhere?
    Any help would be greatly appreciated.
    If something is not clear I can explain in more detail.

  2. #2
    Join Date
    Sep 2002
    You need another table like Transactions for Goods Received (unless you find it useful to combine these into a single table with an IN/OUT column).

    You also probably wany a table to record the level of stock for each Product at each Store e.g.

    - store_id
    - product_id
    - quantity
    - reorder_level (maybe)

    This will get updated for every insert of Transaction or Goods Received row.

    The best way to calculate total sales is to simply sum up the Transactions when required. In a data warehouse you would store these totals for speedy retrieval, but with only 12 stores x 12 products this shouldn't be necessary.

  3. #3
    Join Date
    Jun 2004
    Arizona, USA
    Right. Have an inventory table, showing the quantity and location of the items at a given point in time.

    Then, sum the inventory table with the sum of all transactions (after the time snapshot) which reference that item/location

    Whenever you sell or otherwise move an item out of inventory you add a transaction record, containing the timestamp, item number, the location, and a negative quantity. When receiving an item into inventory, the transaction record contains a positive quantity and timestamp. To move an item from one store to another, you would need two transactions: one removing the item from the first store's inventory, and a second, adding the item to the second store's inventory.

    Whenever you perform a physical count of inventory, you would add an inventory adjustment transaction (so as to make the database inventory match the actual count,) then reset the inventory record with the current timestamp.
    Last edited by loquin; 02-07-08 at 15:06.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts