Database Design help for stores

[leTus]

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:
Products
- id
- name
- price
--------------
Stores
- id
--------------
Transactions
- id
- product_id
- store_id
- quantity
- date
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.
Thanks.

[SheSaidImaPregy]

According to your thumbnail, this is how I would make the databases:

Stores
- StoreID (P)(AI)
- StoreOwner
- StorePhone

Products
- ProductID
- ProductQty
- ProductPrice
- ProductName
- StoreID

Sales
- ProductID
- StoreID
- SalesQty
- SaleDate

Inventory
- ProductID
- StoreID
- InventoryQty

Transactions
- TransactionID (P)(AI)
- StoreID
- ShipmentID
- TransactionDate
- TransactionAmount

Shipping
- ShippingID (P)(AI)
- StoreID
- ShipDate

ShippedProducts
- ShippingID
- ProductID
- QtyShipped

LEGEND [ P = Primary Key, U = Unique, AI = Auto-Increment (AKA Identity) ]

You can now look up the Inventory, and relate the productID to the products table to receive the name of the product, and the quantity. Each Transaction you can grab by the Store, and retrieve the shipping date, what products where shipped, and the quantity.

If you would like, I can build you a program for this. Please contact me VIA PM if you wish.

[leTus]

Here are some more info:
1.

Stores
- StoreID (P)(AI)
- StoreOwner
- StorePhone

I don't need store owner because there is one owner for all 12 of them.

2.

Products
- ProductID
- ProductQty
- ProductPrice
- ProductName
- StoreID

Why do you have StoreID here?? Every store will be selling the exact same products.

3. Also I don't need shippment because the stores don't do that.

So basically I have 12 stores that sell the exact same products. I get every day info of what they sold.

[SheSaidImaPregy]

Products is their current inventory and that is kept store to store, so storeID separates each row accordingly.

Shipment was for you, cause you ship them the goods, don't you?

[leTus]

Thanks SheSaidImaPregy for help, hope you are going to bare through this with me.
I don't think you are getting my idea till the end.
First I don't need shipment because this are stores located on the street people just come in get a hot dog a cola and leave. They don't do shipments.
Second your transactions table I don't think I need it to, I won't keep info about every sale. I just need at the end of the day the info of how much of each product was sold, ex:
Store 1 sold:
cola - 5
hot dogs - 10 and

Store 2 sold:
juice - 10
hot dogs - 10
gum - 20
and so on.

I was pointed out that I can make the transactions table to contain quantity_received and quantity_sold instead of just quantity. So like this:
Transactions
- id
- product_id
- store_id
- quantity_sold
- quantity_received
- date

[SheSaidImaPregy]

I got you the first time, here I'll let you know what I meant and with the edits that you gave me (like no store owner, etc.)

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)
- StoreID (store where it is located, incase different stores have different stuff)

Sales
- 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!)

Transactions
- TransactionID (P)(AI) (need a unique ID for each transaction)
- StoreID (store that the transaction was made for)
- ShippingtID (meant to refer to a shipment, which refers to which products were ordered)
- TransactionDate (Date the transaction was made)
- TransactionAmount (Amount of the transaction)

Shipping (this was for you, cause the supplies gotta get to the owner some how for him to sell them)
- ShippingID (P)(AI) (ID of the shipping record, and to relate which products where shipped)
- StoreID (Which store the shipping record is for)
- ShipDate (Date the shipment was made, or date the owner picks up his supplies)

ShippedProducts
- 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)

The only field that I truly think you can get away with out is shipping table, however you would have to change ShippedProducts's field names ShippingID to TransactionID to relate which products you sold to what transaction.

It is all needed. I have built a management system for inventory, daily cart sales, etc. for a catering company that does 70% of all their business off of events, static cart locations, etc.

This is the minimum that should be on any report, let alone used for information.

And yes, I will stick with you till the end, providing as much help as I can.

[SheSaidImaPregy]

Transactions and Shipping is based off of that you are selling the products to the owner, who in return sells the products somewhere else. If you are the owner and just run the carts, then you don't necessarily need them, no. If it contains anyone else besides you, yes you do!

[leTus]

Hey, I still think your design is too complicated for my needs. Just to be sure we are on the same page:
I have 12 cart stores just like in the picture, which sell the EXACT SAME products, and that will never change. I am the owner. And I have a big warehouse somewhere in the city. Operations go like this:
I receive 10,000 bottles of coke to my main warehouse. I take 100 to each cart. Daily I get from each store just info about what they sold and what they received. That's it.

[SheSaidImaPregy]

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.