So we've got a table called "books" and we want to build records of how
often each book is accessed and when. How would you store such
information so that it wouldn't become a huge unmanageable table?
Before I go out trying to plan something like this I figured I'd ask and
see if anyone had any experience with such a beast.
One idea I had was to create a separate DB for these counters and create
a schema for each year. Within each year schema I would create month
tables. Then I'd write a function to hit whatever schema existed like,
ala...
SELECT * FROM public.get_counters(date, hour, book_id);
get_day_counters would break up the date and based on the year do a
select counters from "2006".may WHERE day=12 and book_id=37. If hour
had a value it could do select counters from "2006".may where day=12 and
book_id=37 and hour=18.
Offline scripts would take care of generating and populating these
tables, as they'd be historical and never real-time.
Thoughts? I'm hoping someone has done something similar and can point
me in the right direction.
- E