Search code examples
sqlinventoryconcept

Inventory Concept - Making the right Database

Im a but stuck on the concept of Inventory.

Here is the business process: The company restocks their medicine every year. The inventories are recorded in a spreadsheet that contains

Beginning Count - Consumed Count - Ending Count

Now here is the crisis. Should I make a table that holds the Beginning count or not?

My Logical reason.

If I dont add a table that records the items stocked yearly

Summing the Consumed and Ending balance will result to the total Inventory. The system is accurate enough to provide us with that information. If the Physical count does not match the System count, then there is human error (Either the encoded number is more than the actual count the other way around).

If I add a table that records the items stocked yearly

Wouldn't there be a data redundancy in my database? but it does provide a reference of what the medicine count should be. but what if there is too much medicine and the system has less? How will the human error be distinguished?

Solution

  • Off the top of my head there are a few ways of approaching this. Here is one:

    • Table for keeping details about inventory items and latest count.
    • Table for keeping records of stock flows