Re: Database design advice

On Oct 22, 2005, at 0:47 , Neil Saunders wrote:

> I'm writing a property rental web application, and one of the metrics
> I wish to track is the number of page views per month for each
> property.
>
> I originally envisaged a table with a column for each month (one row
> per property), for which the relevant column would be incremented each
> time a property is viewed, depending on the month. But this raises
> questions as to the best way to maintain this table (i.e. Create a new
> month column each month) I'd prefer to keep all logic in the database,
> so would prefer not to use a cron job to do this.

I strongly suggest *not* making a column for each month, but rather a
row for each month--or even for each page view for greater
granularity. Databases are a great way of storing raw information and
transforming it into different kinds of summaries, one of which could
be a cross tab of properties and the hits per month. In this case
your raw data is tracking when a page view occurred, so you'd want to
have a table that captures this information: property, timestamp (or
date). Take a look at tablefunc in contrib about how to generate
cross tabs. I've found them very helpful for generating this kind of
summary (though in my case it's been orders per month).

Depending on your performance needs, you may want to generate an
interim table that pre-calculates your totals per month. For example,
if your property view tracking table is

create table property_views (
     property_id integer not null references properties (property_id)
     , view_timestamp timestamp(0) with time zone not null
) without oids;

you'd have a table

create table property_views_per_month as
select property_id
     , date_trunc('month', view_timestamp) as view_month
     , count(property_id) as view_count
from property_views
group by property_id, view_month;

You'd then generate your crosstab from the property_views_per_month
table. (Of course, you'd have to drop and recreate or otherwise
update this table periodically, as it doesn't capture up-to-date
data). This is an optimization step, however, so unless you find that
you need the data faster, you can just use a view and generate the
crosstab from the view, e.g.,

create view property_views_per_month_view as
select property_id
     , date_truc('month', view_timestamp) as view_month
     , count(property_id) as view_count
from property_views
group by property_id, view_month;

Hope this helps!

Michael Glaesemann
grzm myrealbox com