Re: Database design advice

--- "Daniel T. Staal" <DStaal(at)usa(dot)net> wrote:

> 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

i'd just add that you could take the table that Daniel
discusses and populate a second table with addresses
and monthly hits. each address and month would be a
new record in the table (as opposed to a new column).
off hand, i see a link table connecting a table of
months to a table of addresses and a table of years -
but i'm not sure this would be the optimal design.

this would probably only be necessary if you got to
the point that your query on the main table became
time consuming - and you hope to have that problem!
;-)

good luck.

__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com