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.

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

PRICE_EXCEPTIONS
product
price

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.