Newbie Question / Pointers for Inventory for Inventory DB

[boliviab]

Hi

As per the title, im a newbie to access to forgive me if this is basic - im just asking for pointers though really so i can go and learn the right things striahgt away.

Im creating a DB to handle inventory and ordering for a restaurant and wanted some advice really on what things I might need to look out for , common newbie mistakes in an inventory DB etc.

I am already unsure how to handle some things though. particulary with the prices and value of my items

I have products in stock which I will need to enter as an initial inventory. Now some of these may have been purchased at different prices but Im prepared to enter them at a single price per product for simplicity sake. The confusion I have is how do i handle in the design the changing prices of products. for example chicken, is something I buy a large amount of about 3 times per week and the price changes most days , so the value of the chicken i have in stock needs to reflect this somehow. Im sure this is simple for many of you but as im new I would appreciate some pointers on how to get started as I can see this as an important part of the design of the Db


thanks

Ps - let me know if you need more info

 

[binbin]

Almost everyone have encountered the "Price History" concept. In my opinion, you can handle it a couple of ways. You can have another table that saves the price of the item as per date and time and use a combobox list to select the most up to date price based on date and time. Second is you can have combo boxes in the invoice details that autofills the current price record in your items table, basically storing the price in the invoice detail.

It took me a long time to decide which one to use, I decided on the latter. Since it was easier for me and I really did not care for when price changes were made. In my mind, I could use the invoice details in finding out the price trends.

I learned a lot by rummaging through this forum and also other forums, had a few headaches, but now it does not bother me as much...

Good luck...

 

[kipcliff]

It sound like a standard First In, First Out (FIFO) model.

Your order details will provide the value of the items you have purchased, but this is just half of the solution. You will also need details for the items that are removed from inventory (used, lost, thrown away), and have those items' quantities subtracted from the oldest order details that reflect what is in stock, without altering the original purchase quantity values in the order details.