I am trying to add a simple user poll feature (i will intentionally leave the specifics vague) but the schema i have come up with seems to create a problem with table entry explosions for one of the tables.

The relevant portion of the schema is similar to the following (table names in square brackets):

[User]
-id
-screename
-pass
-email

[Poll]
-id
-user_id
-title
-date

[Poll Item]
-id
-poll_id
-name
-votes

The problem I foresee is as follows. If the site's userbase grows to something on the order of 100,000 users (an optimistic upper bound), and I assume over the course of the lifetime of the site, each user on average creates 100 polls. Each poll can have an arbitrary number of entries, but for simplicity I will cap the maximum at 10 items per poll.

100,000 * 100 * 10 = 100 million entries in the poll item table.
---------

I have created many database driven applications, but this is the first time I have had to deal with such a large potential user base. How would 100 million entries in a single table , coupled with the fact that that table will be the most queried, affect the performance of the application?

Are there ways to split entries across multiple tables? Would this not lead to data integrity issues?
Is there a better way to design my schema?

Thanks in advance for any advice! (I hope my question isn't too much of a stupid one)

I think you are doing everything correctly (especially thinking of the long-term effects). Databases are built specifically for storage and retrieval, so I don't think you'll run into problems doing it this way. Keeping the number of fields minimized per table will certainly help. I can think of at least one way to do this less efficiently (storing all 10 fields in a non-normalized table with 100 million rows), but I can't think of a way that would work more efficiently in the long run.
You could create a 'poll' table and a 'poll item' table for each user (using user_id as a prefix for the tablenames), but I don't think that would be much better, especially from a management perspective.

I cannot imagine that you will have 100,000 users create 100 polls.
You may have 100,000 users, but for someone to create 100 polls would be hugely rare.

I'm reminded of an author, I think his name was Lazlo, wrote a book called the "New Science of Networks"... a 100 poll creating user is a super-user...a 'hub' in nson parlance....a rare entity, it won't be average, it will be one in a thousand people.

OK, but I'm not a specialists on websites, so lets say your data is real....100 million rows is fine, its nothing to a database, put an index on the thing, and rock and roll.

If you are using a larger database like oracle, you could partition the data in some way, to make it more efficient to manage....but I think you'll be fine.

Originally Posted by donjaime

Are there ways to split entries across multiple tables? Would this not lead to data integrity issues?
Is there a better way to design my schema?

Yes partition the tables among several tables and set up constraints. Either partition by time or by hash value. Then put each table on a different hard disc.

Why would that lead to data integrity problems? This is fairly common, both SQL Server and Oracle support it automatically.