Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003

    Unanswered: Database Design Issue

    Hello Friends,
    I have to develop a database for website where Advertisements are displayed, Now i have to maintain number of clicks on an Advertisement on hourly basis so that report can be generated. That is number of click on Advertisement 789 between 10 AM and 11 AM.
    What i am trying to do now is that i have a seperate tabel for clicks on Ad. where i store Ad_ID and nuber of clicks, and time duration.
    Now my challenge is that is i store like that than for every ad there will be 24 rows per day. There can be as many as 1000 ads on that site so there will be 24*1000 rows added per day on that table alone.
    Is there a better way of designing it. Because If the database becomes bulky it will take lots of time while retrieving the data.

    thanks in advance

  2. #2
    Join Date
    Oct 2001
    You might consider putting this table in it's own database.
    After each hour the rows for that row will be static so you could have an active table and an archive table. During slow times on the server copy the expired hours to the archive table to keep the active table small. This will give less likelyhood of corruption too.

    You could make single rows with 24 fields for the hours but that wouldn't give much benefit and would cause more difficult coding.

    Instead of updating the row you could insert an entry in a table then create the aggregate overnight.
    This should cause less contention over locks as an update is relatively slow compared to insert.

    Your current design will give around 8 million very small static rows per year which isn't too bad.
    Just make sure that you create aggregate tables for any queries and don't try to query the active table.

  3. #3
    Join Date
    Apr 2002
    Do you really want data for each click? Think about what the final goal is. Maybe you can collect / evaluate the data as it comes in and store it in a summary row. Less data, more information--maybe.


  4. #4
    Join Date
    Jan 2003
    London, England
    Dumping data into a database is not nearly as recource-comsuming as reading data out from the database so you can easily fill it with plenty of rows without meeting any performance-problems. However, when you need your reporst on this you might want to look into some datawarehousing-techniques (i.e. look up OLAP in BOL). And I would also suggest that you only store Ad_ID and the date/time (GETDATE()) it was hit in the database, if you do that then you have all you need. Then you can aggregate the data into day-by-day and hour-by-hour reports say on a weekly or a monthly basis...
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts