Skip site navigation (1) Skip section navigation (2)

Re: Database design advice

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: Neil Saunders <n(dot)j(dot)saunders(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Database design advice
Date: 2005-10-21 22:37:09
Message-ID: 5EE59C1E-B4DC-4911-AFD9-627F9D16669C@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-10-22 02:58:45
Subject: Re: Displaying current query - eliminating <IDLE>
Previous:From: Rieback MelanieDate: 2005-10-21 22:28:39
Subject: Displaying current query - eliminating <IDLE>

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group