Re: Database design advice
От | |
---|---|
Тема | Re: Database design advice |
Дата | |
Msg-id | 20051021180557.14225.qmail@web33302.mail.mud.yahoo.com обсуждение исходный текст |
Ответ на | Re: Database design advice ("Daniel T. Staal" <DStaal@usa.net>) |
Ответы |
Importing data
("Wes Williams" <wes_williams@fcbonline.net>)
|
Список | pgsql-novice |
--- "Daniel T. Staal" <DStaal@usa.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
В списке pgsql-novice по дате отправления: