Creating a Views table... worried about billions of records!!

[ShawnMolloy]

Hi Experts!

While creating a table in a production SQL 2005 database to hold the "views" of a user profiles, I realized that this table will very quickly grow to such a scale that worries me a bit. I don't have exp w/database of this volume. Can anyone who is more weathered in the database world tell me of any cavets of such a table?

Here is the table schema:

UserID INT
ipAddress CHAR(50)
dateAdded DATETIME

This site has millions of users, each user can have potentially hundreds of thousands of views. (think myspace)

Thank you,



Shawn Molloy
Seattle, WA

 

[jbenson001]

If that table holds the user info, then what does the related "Views" table hold? And you say each user can have hundredes of thousands of views?

 

[ShawnMolloy]

I created two "user" tables, one called "USER" that just holds the user's data such as their username, email address, and password; and a second table called "USER_VIEWS" that only holds their views. The reason why I dont increment an int column in the original USER table is because we need to filter by IPADDRESS, and provide detailed info about the views using dates.



Shawn Molloy
Seattle, WA

 

[jbenson001]

I'm trying to get a handle on what you are doing. What is a "view" and why would one user have over a 100,000 of them? What is the structure of the "view" table

 

[ShawnMolloy]

Here is the page I'm workin' on:

http://wisetopic.com/profile/?UserId=1

Each user has a "Profile". Every time a request is made for that user's profile (or a user's image, a video, etc) I am recording that "View", or instance of http request, in a table that corresponds to the type of "View". We needs to only record that "View" if the IPADDRESS of the requester is unique (ie; this is the first time this IP has ever seen this item).



Shawn Molloy
Seattle, WA

 

[jbenson001]

I don't see how unique the IPAddress will be per view since most people browsing have Dynamic IP addresses. But to answer your original question, your table does not have many columns and sql server has no problem holding millions of rows in a single table. However, I would suggest creating archive tables at some point. This way you can get rid of old data in your main views table, and still preserve the data for reporting purposes.

 

[ShawnMolloy]

The number one reason why we're recording ip's is to stop people from hitting F5 numerous times in order to increase their rankings for various things like videos and blog posts. We're checking for duplicate IP views that are consequtive.

Thanks for your input. I feel better already!

Shawn Molloy
Seattle, WA

 

[jbenson001]

Ahh that makes sense then. You could always (schedule) a script that would delete all duplicate entries in the table to get rid of the F5 thing etc.

The DB will be fine. I use to work with a huge Sybase database(Basically the same as SQL Server). We had one table that grew by 25million + each quarter. By the time I left, it was close to or past 1 billion rows.