How to Design ER Diagram for Product Option Pricing Data

database-designdomain-driven-designnormalizationsql

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

enter image description here

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:

enter image description here

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?

Best Answer

  • 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:

enter image description here

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.

enter image description here

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:

enter image description here

  • 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.
Related Topic