Hello guys, I need some help hope you all can help me with this.
So i have 12 little (I don't know what to call them but let's say) stores which are selling the same products (at this time 12). Like hot dogs, cola, gum and other stuff. Every day I am get a slip of paper from each store containing information what and how many products they sold, also how many they got supplied. I attached an image of how it looks. So I have 12 of those every day. So every store is like a little stock too, and besides that I have a big one which delivers to them what they need. I want to make a database which would keep track of:
1. How much of each product was sold, every day and for each store.
2. How much of each product was supplied to them, every day.
3. And of course the revenues and all that.
I was thinking of making some tables like this:
but I not sure this is the best way, this doesn't cover the products they receive and what should I do about the totals of sales, calculate or store the somewhere?
Any help would be greatly appreciated.
If something is not clear I can explain in more detail.
This will get updated for every insert of Transaction or Goods Received row.
The best way to calculate total sales is to simply sum up the Transactions when required. In a data warehouse you would store these totals for speedy retrieval, but with only 12 stores x 12 products this shouldn't be necessary.
Right. Have an inventory table, showing the quantity and location of the items at a given point in time.
Then, sum the inventory table with the sum of all transactions (after the time snapshot) which reference that item/location
Whenever you sell or otherwise move an item out of inventory you add a transaction record, containing the timestamp, item number, the location, and a negative quantity. When receiving an item into inventory, the transaction record contains a positive quantity and timestamp. To move an item from one store to another, you would need two transactions: one removing the item from the first store's inventory, and a second, adding the item to the second store's inventory.
Whenever you perform a physical count of inventory, you would add an inventory adjustment transaction (so as to make the database inventory match the actual count,) then reset the inventory record with the current timestamp.
Last edited by loquin; 02-07-08 at 15:06.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert