Eci,
the usual way is:
create table books (id_book serial, author text, title text ...)
create table access (id_access serial, id_book int4, timeofaccess timestamp,...)
then for every access you write 1 record to access.
A rough estimate: a book may be lent out every hour once, so that is 8544 records per year and book;
IF you expect that table gets "to big", you still can move over to inheritance:
create table access2006 inherits access
create table access2007 inherits access
and put rules on them to make sure the data goes into the correct table when you access only the access table. Google up "constraint exclusion" within the 8.1 release notes / the postgresql documentation.
Harald
On 7/16/06, Eci Souji <eci.souji@gmail.com> wrote: 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
on different matter:
EuroPython 2006 is over. It was a GREAT conference. If you missed it, now you can prepare budget for visiting EuroPython 2007.
