How to design ER diagram to allow for retrieving of product option pricing data?

Question

I'm looking for a way to organize database tables when it comes to handling product option pricing retrieval and also storing pricing history for my use case.

What I have

I have a custom-made product that has various product options and belongs to a product category. I want to be able to query current price for product options, and keep price history for reference/auditing purposes. Category is effectively a "product group".

Each product is custom-made to order, assembled from options upon customer request. There are no pre-configured products and product price is the sum of selected option prices. There is no concept of a single product price.

I want to answer questions like

1. What is the current price for option X of product Y? (price retrieval)
2. What was the price for option X of product Y last year? (auditing & history)

Note: each category (aka "product group") has a valid set of options and each individual product model has its own price for each option. For example, say Category X has option named Y. Product model 10 in category X prices option Y at $10. Product model 20 in the same category prices same option at $20.

What I have

I think the above corresponds well to my needs - I have products which belong to various categories, where products have various options based on various available categories they belong to, and category_has_options defines available valid product option relations.

Price Lookup and History Design

What I am thinking is having two tables like so:

price_lookup table that will allow me to answer question #1.

price_history table that will allow me to answer question #2.

I am having trouble figuring out how to connect those tables to my existing schema. Is there a design that will suit me well for my purpose?

Answer 1

• A price category is the combination of several options and features.
• But those options and features must me applicable/available for those products
• You assemble different price categories out of available options and features for a given product
• You have a price history table ofr each price category, so no price is lost
• No need to have separate price tables

A PNG is worth 1024 words:

Simpler version assumming feature and option is the same thing, added detail on prices:

• Some restrictions cannot be modeled and have to checked with a sanity check procedure like for example that there is not date overlapping for the same price/product/category ih historical table.

Update:

• PRICE_CATEGORY is a master table and OPTION_CATEGORY is it's detail. So a price category is like a combo/product that adds many things to the main product. Those things are in OPTION_CATEGORY.
• The FK from OPTION_CATEGORY to AVAILABLE_PRODUCT_OPT is to make sure tou cannot add options/features to a product that do not apply to it, like adding a ashtray to a motorcycle.
• OPTION_CATEGORY only has FKs to the other two tables

An even simpler model:

• Products and options are in the same table, after all options are products too.
• A column indicates whether a product is a base product or an option.
• Being a single table, a single historical price table is necessary.
• The OPTION table is a many to many between products and themselves indicating which options are theoreticatlly available for each product (no astrays for motorcycles)
• A trigger on OPTION must garantee that only base products have options.
• PRODUCT_COMBO is a named combo which consistes of a base product and one or more options.
• Obviously only options relevant to a product can be added to a combo.

Answer 2

Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live

Creating 3 different pricing tables and expecting developers to know which one is relevant 9 months from now is asking a lot.

Personally, I'd go with

price(id, price, start_date, stop_date)

and have a separate table for features:

productPrice(id, productId, priceId, option)

Using just a timestamp for your price has the benefit of not having to worry about 2 price records with overlapping dates. However, it has the disadvantage that now your price records depend on each other - lose a single record and you know don't know the historic price anymore.

Answer 3

How about this:

price_lookup serves as an authoritative source for product-category-option definition.

price_history defines pricing for said product options.

Or this:

price_history serves as authoritative record of product option and also stores pricing history by date. Best of both worlds!

To get current product price:

select amount
from price_history 
where product_id = X and option_id = Y 
and date_from <= NOW() and NOW() <= date_to;