Okay, then this is what I would recommend, and yes it is more complicated than it has to be, since you're the owner.
Stores
- StoreID (P)(AI)
Products
- ProductID (ID to relate to other tables)
- ProductPrice (price to calculate the total of sales)
- ProductName (name of the product, like hotdog)
Sales (Only entered at end of day, total sales for each product.)
- ProductID (relative to product information, used to retrieve price and product name)
- StoreID (relative to which cart or store operating)
- SalesQty (number of sales for the day, this is automatically calculated from inventory counts)
- SaleDate (Date this sale record was created)
Inventory
- ProductID (relative to keep inventory of each product)
- StoreID (store/cart where the inventory record is for)
- InventoryQty (how much in inventory!)
ShippedProducts (This is for their receiving, so you know how much they received.)
- ShippingID (The id to the shipping record, which relates to the transaction records)
- ProductID (The product id that was shipped to the owner for him to sell)
- QtyShipped (how much of each product was shipped)
This is how I would do it:
Build your stores in the database (all 12). Then put the products you sell in the products table. Give them each a unique ID, the name of it, and the price each one sells for.
Then put the inventory of each cart in the table "Inventory". This will keep track of their current inventory. This is used to relate to how much they received, and their total sales at the end of the day.
For ShippedProducts, this is how much you sent each store on that day (100 bottles coke).
For sales, this is calculated by what their ending inventory is on that day, compared to their ending inventory on the previous day with the new shipment.
Now you can easily get away with without shipped products, but then you will have to keep a count of how much you shipped each day.
This is a very efficient way of doing this, however, you can easily combine it all into one table if you wish also, it's just more problematic.
And you keep employees out of habit of calculating it all themselves, which will lessen the amount of shrinkage.
