best database diagram for this idea?
designing a database for a shop and i faced a little problem. Let's say there is a product x in our shop which has a purchase price and benefit to get retail price . But there is another supply to the shop of the same product bu wih difference price . 1- According to the shop owener he needs one price for the product. 2-should i seperate product info from the shippment say i put table of productid - name -reference and other table ofr SuplierInvoice - Producyid- Quantity - PurchasePrice
again should i put seeling price in the first table of product or the other one and what about actual quantity where should be put?
Please i need ideas ....
Having built a few systems that are for ordering / costing I can confirm that there are many ways of doing this.
The way that I'd usually start with is to have the concept of
- Product
- Supplier
- SupplierProducts
The Product identifies the product that you are using; the Supplier are your Suppliers and the SupplierProduct is used to provide specific pricing information for a product; as a product must come from a supplier.
Depending on the requirements; you'd either have orders / invoices that relate to the SupplierProduct - or if you are selling directly from the stock - then you would place SupplierProducts into stock with their Sale Price and Cost Price set appropriately based on the stock purchase
You then sell items from the stock table; Because the item has come from stock the pricing information in the stock table reflects the actual price paid when the item was put into stock. You'd have multiple entries in the stock table for the same product / supplier containing the details as each product was purchased, and maintain the current stock level (although this can lead to update problems and usually I'd have a stock table and a stock movements table, and use these together to decide stock holding)
