Storing e-mails in a SQL database

Question

I want to create a simple messaging application that stores the messages (similar to e-mails, but simply messages). How should I design the database?

Table Users:

• Username (primary key)
• Userpassword

Table E-mails:

• EmailID (Primary key)
• From (Foreign key to user)
• To
• Owner (Foreign key to user)
• Subject
• Time
• Email content
• Email layout (Possibly content and layout in one field? XAML)

Since an e-mail can be sent to multiple people, what would be the best way to store the to column? Should I simply put it as a string, separated by commas, then retrieve all the users with a function in my code? Or is there a better way to go about this?

Answer 1

Table Users

• UserId
• Username
• Password

Table Emails

• EmailId
• From (Foreign key to UserId)
• Owner (Foreign key to UserId)
• Subject
• EmailContent
• EmailLayout
• Time

Table Recipients

• Id
• Email (Foreign key to EmailId)
• To (Foreign key to UserId)

So the recipients of the email are linked to the email as a many to one mapping.

This way you can select all the recipients of an email by selecting all the rows in the Recipients table with the appropriate EmailId

For example an email sent to 2 users will have rows (as an example)

---------------------------
| Id  | Email | To        |
---------------------------
| 1   | 1     | 3 (User1) |
---------------------------
| 2   | 1     | 4 (User2) |

Is storing a delimited list in a database column really that bad? Gives good examples as to why using comma separated values in a database table is bad practise.

Answer 2

Table Users

• Username (primary key)

• Userpassword

Table E-mails

• EmailID (Primary key)

• From (Foreign key to user)

• Subject

• Time

• Email content

• Email layout (Possibly content and layout in one field? XAML)

Table Email_Recipients

• Recipient ID (Primary Key)

• RecipientUserID (Foreign key from User Table)

• EmailID (Foreign key from Email Table)

• RecipientType //Types can be Actual, CC, BCC

Another table can be created of EmailRecipientTypes

Like Table EmailRecipientTypes

• TypeID (Primary Key)
• TypeName //can be Actual, CC or BCC

In this way you can modify the Table Email_Recipients as

Table Email_Recipients

• Recipient ID (Primary Key)
• RecipientUserID (Foreign key from User Table)
• EmailID (Foreign key from Email Table)
• RecipientTypeID (Foreign key from Table EmailRecipientTypes)

Although the fourth table will only contain 3 records but it will help in reducing in the data replication and will help you in grouping emails in some required way that you want (its a maybe)