Monthly Restaurant Inventory Design

[Baxtula]

Hello,

I am a computer science student working as a bartender in a restaurant. My boss was complaining that his monthly inventory audit was taking too much time (as he has about 2000 unique items to count) and I suggested I could build a mysql database to help him (and hopefully get me more hours working building this for him)

I wanted a little advice on some critical aspects before I invested too much time in this.

As an overview my process is this.

I'm using my bosses android phone's bar-coder scanner app to scan an item. Using this app, once the barcode is read, he can enter a quantity for that item. I'd like to add that he isn't tracking incoming or outgoing inventory, just what's on hand at the end of the month.

I am then building a small app to transfer that data to a MYSQL database.

I figured I'd need an INENTORY table with four fields:

1) InventoryID - which is an automatic increment field
2) barcode - which is also a unique identifier
3) quantity
4) timestamp field

I will have corresponding tables with product information that relate back to the Inventory table based on the unique barcode.

He will then run and print off monthly reports based on queries (generated by PHP) He can go back and check monthly inventories using a timestamp range query.

Is this a good way to do a monthly INVENTORY TABLE
or should I create a few dozen separate inventory tables based on month, such as NOV_INV_2011, DEC_INV_2011, etc... or perhaps is there another solution?

Once again, I'd like to stress that incoming and outcoming inventory are not accounted for... he is only counting what's on hand at the end of each month.

Thanks for any advice.

Joel

[smantscheff]

All needed information is in the inventory table. There is no need for monthly tables as you can filter data by their timestamps. To avoid checking of double entries you could define a month's inventory as all entries with a timestamp from the this or following month (depending on the time of inventory taking), grouped by barcode and with only the newest item valid. Like in

select barcode, quantity, tstamp from inventory i1
where year(tstamp)=2010 and month(tstamp)=10
and tstamp = (
  select max(tstamp) from inventory where barcode=i1.barcode and year(tstamp) = 2010 and month(tstamp) = 10
)

as a query for the october 2010 inventory.

[Baxtula]

Thank you Smantscheff, that is very helpful!

[smantscheff]

My first try at the query is redundant. Better:

SELECT barcode, quantity, tstamp FROM inventory i1
WHERE tstamp = (
  SELECT max(tstamp) FROM inventory WHERE barcode=i1.barcode AND YEAR(tstamp) = 2010 AND MONTH(tstamp) = 10
)

[Baxtula]

Thanks smantscheff! It works flawlessly!