CODEWITHSUNDEEP
sqldatabase
EVL
EVL

Reputation: 53

Database design for products with multiple prices

What would be the best database design for this:

I have a list of products and a list of clients. Each product has assigned 9 different prices Each client has assigned one of those prices for every product.

IT would be something like this:

PRODUCTS
id
name
price1
price2
price3
price4
price5
price6
price7
price8
price9

CLIENTS id name price_Code

Any idea?

Upvotes: 3

Views: 5286

Answers (3)

MinhD
MinhD

Reputation: 1810

You need PRODUCT_PRICE table which stores any number of price.

And PRODUCT_PRICE_CLIENT table stores which client buy on which product price.

Remove price1 price2 price3 price4 price5 price6 price7 price8 price9 from PRODUCT table.

Upvotes: 1

Rob
Rob

Reputation: 11733

This makes no sense. Prices are examples of diachronic properties: meaning they represent a value at a given time.

What you want is a price table with 2 dates in it. 

Prices
  productID:long
  effectiveDate:Date
  endDate:Date

Then when you want the current price, you have two choices. The most common one is to join the row that has a null for the endDate field.

The other reason you want to do this is that you might be asked to change the price of a product on a given date.

Upvotes: 2

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51445

Some information is missing.

First, you need a product table

Product
-------
Product ID
Product Description
...

Next, you need a product price table

ProductPrice
------------
ProductPrice ID
Product ID
Price

Next, you have a client table

Client
------
Client ID
Client Name
...

Finally, you have a client product price table. The missing information is, does the price for the client change if the price changes in the product price table?

ClientProductPrice
------------------
Client ID
Product ID
???

If the price for the client changes, then the ??? is the Product Price ID. You don't need the Product ID because you can get that from the product price table.

If the price for the client does not change, then the ??? is the Price.

Upvotes: 5

Related Questions