5

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?

Constantino Tsarouhas
  • 6,846
  • 6
  • 43
  • 54
user3117628
  • 776
  • 1
  • 15
  • 35
  • If all recipients are within the model (so message to outside), then you should store them as a reference table, not inside the message table at all. That way you get around parsing the entry and all addresses are always up to date. – arkascha Aug 14 '16 at 10:06
  • 3
    And a general not, just to make sure you remember that: You should _never_ store a user password inside a database. – arkascha Aug 14 '16 at 10:07
  • @arkascha I'm supposed to store the password in the database, as hash unfortunatly. – user3117628 Aug 14 '16 at 10:27
  • *Should I simply put it as a string, separated by commas* good heavens **NO!** - there is **always** a better way than this! – marc_s Aug 14 '16 at 10:34
  • 1
    Storing a hash of passwords is perfectly fine. Take care to use a good algorithm though, not just some... – arkascha Aug 14 '16 at 10:48

2 Answers2

7

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.

Community
  • 1
  • 1
Roy
  • 3,027
  • 4
  • 29
  • 43
5

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)

Umair Farooq
  • 1,684
  • 2
  • 14
  • 25