many tables vs one table

Alrighty, for you database optimization junkies out there, I am running Win 2k Server and Win 2k Enterprise MSSQL Server.

I am undecided as to the best course of action to pursue for an application I am writing.

The application would need to store a massive amount of data for each user.

A) I could write it such that, for each user, it would create a user specific table, and use that table for all data transactions for the user. This would optimize searching, joining and selecting, however, there is an upper limit on the number of tables that can be used in addition to the extra storage space and table loads required by the server. This would increase paging as tables would have to be dismissed from memory and then reloaded, etc. Server performance could suffer tremendously if the server was very active.

B) I could write it such that there is a table general enough for all users to use, and would keep them seperate programmitcally using USERIDs or some other such unique identifier. However, this would provide the easiest interface, have the lowest impact on storage space, but it, too, would present memory issues as the table grew too large to keep in memory all at once. Searching, joining and selecting performance would also suffer as the table grew.

Does anyone have pros/cons for either A or B that I haven't thought about? I'm leaning towards A, because I believe if the server runs out of namespace for new tables, I can convince the client to spring for an additional server (or branch off into different databases, depending on server strain)--I mean they're not THAT stingy...

Option B is the way to go...having a table for each user would be a nightmare to adminster. My company has a DB with over 70K users and we do not experience excessive server load or memory usage. MSSQL was designed for relational databases, not single "flat file" databases, so I would use it as such.

Just my two cents

Thanks. That's what I wanted to know.