User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 426,317 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,338 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser: Programming Forums

Database Design help for stores

Join Date: Sep 2007
Posts: 1,057
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Database Design help for stores

  #6  
Feb 8th, 2008
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.
Reply With Quote  
All times are GMT -4. The time now is 10:42 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC