Database design for web mail client

Question

I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue.
Lets say this application has pottentially 100 000 users (maybe more than this). each user can have thousands ( n*1000 ) of emails in there inboxes.What my question is...

Is it okay to create inbox table for each user dynamically OR create only one inbox table to all users?

If I use only one inbox table it will contain 100000*(n*1000) records.If I do other way only particular user's e-mails will stored in their respective inbox table. But there will be thousands of inbox tables in the database.

Can any one suggest a design clue...

• Dan Guzman: The single inbox table is more correct from a modeling perspective. With a composite clustered index with the registered user as the leftmost key column, performance will be good. 100M rows isn't that big in today's world.

Answer 1

I think it has to do with your security requirements / permissions on the backend. With the single table approach, DB permissions will be for the table, whereas you can permission the multiple tables per user.

You can partition your single inbox table by user. This will allow you to manage it in much the same way as you would the multiple tables for performance.

If you decide on separate inbox tables per user, I would recommend putting them all in their own dynamically created filegroup.

What will happen to the tables or rows when the user leaves? In two scenarios, 1 where they are dropped and 2 where there are archived, both would be more easily and cleanly accomplished by the table scenario IMHO.

Answer 2

I would propose the following table structure. It would be best to maintain the Inbox table separately as Dan Guzman pointed out in his comment. I would prefer to have a Foreign Key on the User Id to explicitly tell that this record belongs to what user and would be easier to query!

[User]
======
UserId (PK)
Inbox
=====
MessageId (PK)
UserId (FK to User table)