Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Help With Table Structure

    Hi all! I'm new here, so please excuse my ignorance ;p

    I do the produce ordering for a restaurant and I'd like to create a database to track the prices of the items that I'm buying.

    Currently, each week I have to look over all the prices on the invoice from our distributor and manually (by comparing with previous invoices) spot price increases or anomalies. As you can imagine this is a pain!

    So initially I built a spreadsheet. The left most column has a description for the item, then each column to the right of that I use to store the items price for each invoice. It looked basically like this....

    DESCRIPTION | 11/15/2009 | 11/18/2009 | etc ......
    ------------------------------------------------------------------------
    Potatoes | $18.95 | 19.10 | etc ......


    This worked OK for a while, but now I'd like to move to using a database so that I can do a bit more expansive reporting (and possibly later include multiple distributor pricing comparisons!). However, I don't know enough about database design to know how to relate the tables to store the historical data.

    My initial thoughts were...

    ITEM_TABLE
    -----------------
    ITEM_ID (unique ID for each item)
    ITEM_DESCRIPTION
    ITEM_PACK (amount of items in a case)
    ITEM_SIZE (units of measure for the container)


    PRICE_TABLE
    -----------------
    ITEM_ID
    PRICE_ID (unique ID for each entry in the PRICE_TABLE)
    PRICE_DATE
    PRICE


    Each item would be entered into the ITEM_TABLE only once. Then, for each invoice there would be a price entered into the PRICE_TABLE for each item. So the PRICE_TABLE would contain multiple prices for each item and have a date associated with the price.

    Does this make sense? It doesn't really make sense to me, but it's the only thing that I can think of... I guess I'm having a hard time thinking about the data being returned in multiple rows instead of columns (like I had in my excel spreadsheet).

    Any help would be greatly appreciated, as I've been thinking about this for days and I'm pretty much stuck, and don't have anyone to talk to for advice

    Thanks

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Yes, it makes sense. You are also going to want a Supplier table and add Supplier_ID to the Price_Table.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by TrikNik View Post
    Currently, each week I have to look over all the prices on the invoice from our distributor and manually (by comparing with previous invoices) spot price increases or anomalies.
    So the real question is how much work is it now to do all this?

    To get a report on your anomalies with the structure you have, it should be relatively straightforward:

    Code:
    -- Finding the item, its price now and its price last week
    SELECT it.ITEM_DESCRIPTION, pttw.PRICE as PRICE_THISWEEK, 
      ptlw.PRICE as PRICE_LASTWEEK
    -- Match items between this week and last week
    -- Your date handling will probably be trickier than this
    FROM Price_Table pttw INNER JOIN PRICE_Table ptlw 
      ON pttw.PRICE_DATE = DATE_ADD(ptlw.PRICE_DATE, 7) 
      AND pttw.ITEM_ID = ptlw.ITEM_ID
    -- Join in the item table
    INNER JOIN Item_Table it 
      ON pttw.ITEM_ID = it.ITEM_ID
    WHERE pttw.PRICE_DATE = TODAY()
    -- Finds prices that changed by more than 20% either direction.
      AND NOT (pttw.PRICE / ptlw.PRICE BETWEEN 0.8 AND 1.25)
    ORDER BY ITEM_DESCRIPTION
    The fact that I can write that query based on the tables you provided is a good sign. If I read you right, that's pretty much what you were doing manually earlier.

    I'd say your next step, then, is going to be figuring out how to make data entry easy. That's often much more trouble than designing the perfect schema.

Posting Permissions

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