I have 3 tables (see below), Table A describes a product, Table B holds inventory information for different dates, and Table C holds the price of each product for different dates.
Table A
------------------
product_id product_name
1 book
2 pencil
3 stapler
... ...
Table B
------------------
product_id date_id quantity
1 2012-12-01 100
1 2012-12-02 110
1 2012-12-03 90
2 2012-12-01 98
2 2012-12-02 50
... ... ...
Table C
-------------------
product_id date_id price
1 2012-12-01 10.29
1 2012-12-02 12.12
2 2012-12-02 32.98
3 2012-12-01 10.12
In many parts of my java application I would like to know what the dollar-value of each of the product is so I end up doing the following query
select
a.product_name,
b.date_id,
b.quantity * c.price as total
from A a
join B b on a.product_id = b.product_id
join C c on a.product_id = c.product_id and b.date_id = c.date_id
where b.date_id = ${date_input}
I had an idea today that I could make the query above be a view (minus the date condition), then query the view for a specific date so my queries would look like
select * from view where date_id = ${date_input}
I'm not sure where the appropriate level of abstraction for such logic is. Should it be in java code (read from a pref file), or encoded into a view in the database?
The only reason I don't want to put it as a view is that as time goes by the join will become expensive as there will be more and more dates to cover, and I'm usually only interested in the past month's worth of data. Perhaps a stored proc is better? Would that be a good place to abstract this logic?
