CODEWITHSUNDEEP
sqlsql-serverperformancedatabase-designdynamic-tables
Aslam Jiffry
Aslam Jiffry

Reputation: 1316

Database design for web mail client

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 their 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...

Upvotes: 0

Views: 514

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This is a harder question than first appears. Normally, the inclination is for one table.

However, we are talking users and emails here. In SQL Server (as with other databases), it is much easier to handle permissions at the table level rather than at the row level. So, that is one consideration that might push you to multiple tables.

That is despite the fact that multiple tables are difficult:

  • Changes to the data structure need to be replicated a zillion times.
  • SQL is designed to handle really big tables, but not necessarily lots of them (although a few thousand shouldn't pose a problem).
  • Simple queries such as "How many unread emails does each user have?" become almost impossible.
  • Foreign key references to the inbox table are not possible.

Upvotes: 2

Related Questions