Help With Table Structure

TrikNik · #1 (**permalink**) 11-24-09, 03:19

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

MarkATrombley · #2 (**permalink**) 11-24-09, 04:04

Yes, it makes sense. You are also going to want a Supplier table and add Supplier_ID to the Price_Table.

sco08y · #3 (**permalink**) 11-25-09, 21:41

Quote:

Originally Posted by TrikNik

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech