Re: Database design advice

On Fri, October 21, 2005 11:47 am, Neil Saunders said:
> Hi all,
>
> 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 then thought of using a trigger to check if the relevant column
> existed before attempting to increment, but this would only be
> utilised once, and then just create overhead at every update in the
> future. Should I just create a table with 10 years worth of columns?
> Is there a different alternative entirely?
>
> None of the above strike me as architecturally sound, and so any
> advice from someone more seasoned in database design would be
> gratefully received.

Are you tracking anything else per page/property view?

My first thought would be to have a separate table of 'view' information:
When, what property, and whatever else you want to track.  Then, if you
want to know how many times a particular page/property was viewed you just
do a select on that table for all the records related to it during that
time period and count the rows.

As a bonus, you aren't limited to any particular time interval, and can
add fields in the future.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------