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
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.