Hit Summaries

What I do for such tallies is create a table similar to the following:

create table activity_log (object oid, activity oid, activity_time
timestamp)

With an additional summary table:

create table activity_summary_log (object oid, activity oid, month int,
year int,  instances int);

Periodically, I summarize my tallies into the summary log:

insert into activity_summary_log (object, activity, year, month,
instances) select object, activity, extract(year from activity_timestamp)
as year, extract(month from activity_timestamp) as month, count(*) from
activity_log where extract(month from activity_timestamp) < extract(month
from CURRENT_TIMESTAMP) group by object, activity, month, year;
followed by (in the same transaction)
delete from activity_log where extract(month from activity_timestamp) <
extract(month from CURRENT_TIMESTAMP)

Then to get all my summarized data (even the ongoing tallies), I have a
view which grabs from the summary tables and summarizes the activity_log
table.  I like this setup because:

a) If my summarizing script doesn't function, I haven't lost anything.  I
just run it again when I feel like it

b) If my summarizing script is called too often, it's fine

c) I can record additional information about each transaction in my
activity log for audit purposes, which get automatically removed when I
summarize everything

Jon

On Sat, 25 Jan 2003, Alan T. Miller wrote:

> I have a farily general question about structuring a database, and
> considering this is the 'general' mailist for postgresql my database of
> choice for the project I am working on, I thought this would be a good place
> to start. Please forgive me if this should be posted elsewhere. Of course a
> hint on where it should be posted would be great if this is not the correct
> place to ask this.
>
> Anyway, I am trying to come up with a logging table scheme for an
> application that indexes rental properties around the world. Currently we do
> not have anything in place in our current database tables to keep an
> accurate ongoing tally of the hits each of these properties gets on any kind
> of time interval.
>
> What I do have is a table that keeps a running total for each property. The
> way that is implimented is through a javascript function that makes a
> request to a script that then updates the database table containing a unique
> id for that property and then ups its count for each hit. So we have a table
> that looks like...
>
> TABLENAME:     Hits
> TABLE COLUMNS:     ID_Property | hit_total | last_updated
>
> However, we want to expand this and be able to keep a running tally of hits
> per week, month, day perhaps. But I am having trouble wrapping my head
> around the best way to go about doing so. A sensible table scheme escapes
> me.
>
> My first inclination was to create a table as follows...
>
> TABLENAME:     Weekly hit total table
> TABLE COLUMNS:     ID_property | week 1 | week 2 | week 3 | week 4 | etc...
>
> That table would be populated via an automated script that would tally up
> the weekly totals for each property based upon the number of hits drawn off
> of a modified version of the hits table described earlier. In other words,
> the current hit counter table would be cleared every week to make way for
> the tally to start fresh for the next week.
>
> The problem with the above scheme however is that I would be adding a column
> to the table every week, and it just seems like bad design to do so. There
> must be a better way.
>
> To avoid having to create a table that requires that I alter its structure
> every week, I thought of the following solution but It seems overkill, as it
> would add a huge number of tables. Anyway, this scheme would have a seperate
> table for each property with a structure something like the following.
>
> TABLENAME:     Hit Totals for Property 'foo'
> TABLE COLUMNS:     Week | weekly hit total
>
> For every week there would be a new row added to the table, again, it would
> be populated by a cron script. However, I do not like this solution as I am
> not interested in having 5000 tables set up for all the properties, and then
> each time one of the properties were added or deleted I have to alter the
> database to add or delete tables.
>
> to sum it up....
>
> Solution 1 which provides a unique row for each property, seems inadaquate
> because it will require adding columns to the database on each update, thus
> altering the table structure on a constant basis.
>
> Solution 2 which provides a unique table for each property in the database
> seems inadaquate for the same reason as above, I am altering the database
> itself all too often, not to mention I have to deal with thousands of
> tables.
>
> Considering postgreSQL is an object oriented database, and I am new to that
> concept in databases, I was hoping maybe someone here had a workable
> solution or suggestion that may or may not take advantage of PostgreSQL's
> object oriented features. Any help or suggestions on this would be very
> highly appreciated.
>
> Thanks,
> Alan
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>