Hi friend,
i have a problem related to the design of a DB, and i hope someone will help me out.
It's all about a company (let's call it COMODO) that sales products. COMODO has 4 departments.
Each department has its own stock which is supplied by the main stock of the company (like a principale stock that supplies sub-stocks).
We need to keep records of the main stock's inventory of COMODO. And we also need to keep records of the stock of each department.
Sales transactions must be recorded. Inventory in-flow and out-flow (for the main stock and the departments' stocks) must also be recorded.
For every department we need to have a report displaying the information:
- item name, item stock before sale, total quantity sold, and item stock after sale for every product available in the department stock.
We also need to have a more general report displaying:
- Department name, item name, item Stock before sale, Total quantity sold , and item stock after sale per department for every item. And group it by department.
What could possibly be the best design to have everything working perfectly? I know some part should be handled
at the frontend application level, but i believe a good design will make think smoother.