2

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?

  • You have the arrows pointing the wrong way. Arrows go from the many table to the one table, not the other way around. – Tulains Córdova Sep 2 '16 at 17:07
2
  • 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.
  • thanks but wow, 9 tables(!!) For my purposes feature and option are probably more or less interchangeable, aka. different names but similar concept - an add-on to the base product. Otherwise if I become adamant about naming them exactly, each product category has different names for those add-ons -- there are probably 20-25 different add-ons across all product lines with their own product-specific names. As much as I like the conceptual decomposition, I am thinking 9 tables is a bit much... – Dennis Aug 16 '16 at 17:07
  • Also I didn't see how price_history work just yet, but it basically needs to answer questions like: what's the current "active" pricing for this particular product & its options? who made a change to the pricing, and when – Dennis Aug 16 '16 at 17:09
  • fyi - see Note/Example update on my question – Dennis Aug 16 '16 at 17:16
  • @Dennis I added a simple model with 6 tables and detail on how prices work. – Tulains Córdova Aug 16 '16 at 17:30
  • @Dennis with the update it would go beyond 9 tables because you need historical prices for both base products and options. – Tulains Córdova Aug 16 '16 at 17:38
1

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.

1

How about this: enter image description here

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

price_history defines pricing for said product options.

Or this:

enter image description here

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.