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
- What is the current price for option X of product Y? (price retrieval)
- What was the price for option X of product Y last year? (auditing & history)
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?