Database design for Time Series
I am new to this and I have a design question. I have a flat yearly dataset on prices of many products that I would like to turn into a relational database, but many products undergo changes in how they are categorized.
Lets say apple prices are listed in 1928, but then in 1935, red apples and yellow apple prices are listed. In 1940, yellow apples are again split into large yellow apples and small yellow apples, so in 1940, we have red apples, small yellow apples, and large yellow apples.
I would like a relational database where I can look up "red apples" for 1928, and get the rate for "apples" in 1928, and "small yellow apples" for 1935 and get the rate for "yellow apples"
I am thinking something like the following would work.
Table 1: Product ID, Product Features.
Table 2: Yearly price data
Table 3: Table of product "categories"
But I don't know how to relate these tables or solve the problem that some products have prices for some years and are themselves categories for other products.
Thanks for your help.