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…
Best Answer
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.