CODEWITHSUNDEEP
c#sqlemail
SeToY
SeToY

Reputation: 5895

Mail-Tables in SQL: To split or not to split?

I got a Table Mails that stores mails (who would've thought... ;)).

With a tinyint MailStatus I decide whether that is a SentMail, Draft or ReceivedMail.

Now I was wondering if a Table-Split wouldn't be more appropiate, said having:

MailsSent

MailsDraft

MailsReceived

Instead of the MailStatus tinyint-flag.

But that'd also mean I need to move rows (insert in one table, delete in the other) when a user deletes or moves a mail, instead of just switching the int-value.

What are your thoughts on this?

Upvotes: 1

Views: 87

Answers (3)

ntziolis
ntziolis

Reputation: 10231

From a code perspective it is much simpler to handle an enum than to handle different types.

And from a db perspective the reasons for storing this very similar data in different tables are:

  • performance issues
  • size issues (single table grows too large)
  • db normalization concerns (null values for SentDateTime field etc.)

In most cases you will never hit any size/performance issues, so it's a question of how normalized you need your data to be. If you don't really care about that don't make it more complicated than it is: Leave it in one table and use an enum (int) in the table to differentiate the status.

Upvotes: 2

Jaques
Jaques

Reputation: 2287

Personally I would keep the flag, and Index it. That way even with a huge table you would be able to retrieve the information from the table fast enough. You can only change the flag if the mail moved from one folder to another which will put less strain on the server and the Log file.

Upvotes: 2

Roland Mai
Roland Mai

Reputation: 31097

Since email can be categorized into folders, I suggest you create an additional table MailFolders that stores such folders/email status.

Then use a foreign key in your Mail table to the MailFolders. You can designate some of the folders as system folders and others as user folders (and include a userid foreign key).

Again, it all depends on your application design and requirements.

Upvotes: 2

Related Questions