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](https://i.stack.imgur.com/YGken.png)](https://i.stack.imgur.com/YGken.png)

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](https://i.stack.imgur.com/UlV3Z.png)](https://i.stack.imgur.com/UlV3Z.png)

`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?