I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month.
Question: Because every product can have different prices over time, I am thinking of storing all the data in a single table, each row having full details of the product, its price at that time, and the time.
id | name | color | manufacturer | date | price
123 Socks 123 black nikki 12-12-2012 123.70
124 Socks 123 black nikki 12-01-2013 145.67
125 Graphic Tee xXx red departed 13-01-2013 250.80
My reason for storing everything in one table and have duplicate data in certain columns like name, color, manufacturer
is to facilitate doing queries like fetching all products' prices in a particular month (can return 10k rows) will avoid costly table joins if I have a table with the product details and a foreign key in another table with just the time-cost data.
Is this a good reason for doing what I've described? Will using hstore
make it easier to do such queries? Or did I get it all wrong?
Thanks!!