Hello everybody,

I am designing a table for products. The products belong to one of three classes a, b and c. Each class specifies a price (for products).

I need to design the tables such that changing the price of products will only require changing the price in the class table and not for products individually.

So far I have a table for classes and price, and another table for products with a column that specifies the product class. Does this sound right?

This is a simplified version of the real scenario. Can anybody help?

I thank you in advance.

8 Years
Discussion Span
Last Post by bigakis

Yes it is correct if you are 100% there will be no exceptions. In the most cases the group pricing applies to almost 100% of the products. BUT the rest near-zero% will blow out your design.
What can you do?
I think is very simple.
Create another table called price_exceptions


when looking for a price consult first this table and if there is no record in it, then consult the product_category pricing.

I have this solution (well much much more complex but the principle is the same) running very effectivelly in a pricing and loyalty application. It is not 3NF but it works.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.