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....
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_ID (unique ID for each item)
ITEM_PACK (amount of items in a case)
ITEM_SIZE (units of measure for the container)
PRICE_ID (unique ID for each entry in the PRICE_TABLE)
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
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:
-- 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.