Re: Scaleable DB structure for counters...

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(dot)souji(at)gmail(dot)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.