Re: Database design advice - Mailing list pgsql-novice

Apologies for the delayed reply - Thank you all for the advice.

I'll get on to installing crosstab tonight. I really like the idea of
dumping each page view to a table and creating a view to present that
data, but since this data will be used to render a graph every time a
user logs in I've got this feeling that this will prove a bottleneck
if (when!!) the site becomes successful and the query starts to slow
to a crawl. Of course my fears are totally without substance, but I'd
rather spend more time thinking about the design than undo-ing a bad
design mistake in the future.

Thanks once again for you input!

Kind Regards,

Neil.


On 10/21/05, Michael Glaesemann <grzm@myrealbox.com> wrote:
>
> 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
>
>