1

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 ....

Szymon
  • 42,577
  • 16
  • 96
  • 114
Batlefieldprince
  • 45
  • 4

2 Answers2

5

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.

Simple Supplier Product model

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)

With Orders

Richard Harrison
  • 19,247
  • 4
  • 40
  • 67
  • Unfortunately, this model probably won't suffice in practice. Orders are generally expected to _keep_ the information they had _when_ they were made. E.g. the price used in the order line should not change after the order has been placed, even if the product price changes. For some ideas how to handle that situation, take a look [here](http://stackoverflow.com/a/11930467/533120). – Branko Dimitrijevic Nov 02 '13 at 11:09
  • Because the item has come from stock the pricing information in the stock table reflects the actual price paid. You'd have multiple entries in the stock table for the same product / supplier containing the details as each product was purchased. Also for brevity I'd omitted the fact that the Order and OrderLine would contain the cost / sale price, customer etc; I've amended the diagram as you're right it should be in there. – Richard Harrison Nov 02 '13 at 11:45
  • Which application did you use to create the diagrams? – Tony Nov 02 '13 at 12:02
1

I would say that products table should keep something like average price for a product, so we could easily tell how much we need to spend if we need to order more of that product.

However, since product price is inherently volatile, exact price is not property of the product itself, but is agreed upon every buy or sale transaction that your shop makes. In other words, exact product price and quantity sold or bought should belong to invoices table as part of every transaction you make.

mvp
  • 111,019
  • 13
  • 122
  • 148