CODEWITHSUNDEEP
mysqldatabaseperformancedata-structures
M Smith
M Smith

Reputation: 430

What is the optimal way of setting up a database for a messaging/email application?

I am currently trying to create an email style web app to allow users of my site to contact one another. I have created an SQL table for this, which has the following headings:

I have started to now realise that this table is insufficient. For example, if the conversation has been starred by a user, this does not tell me which of the 2 users has starred the convo, etc.

Can anyone suggest a way to avoid the above issue, and maybe suggest a better database structure?

Upvotes: 1

Views: 56

Answers (2)

Akru Bas
Akru Bas

Reputation: 38

I would recommend split your table into two, let's call them "message" and "star". So, they are:

message
-------
id
sender_id
recipient_id
timestamp
read
archived
deleted
convo_id
parent_id

star
----
message_id
user_id
timestamp

As you can see, I added parent_id into message. If you don't need hierarchical structure - you may kick this column. A star table gives possibility to enhance starring feature. Who knows, maybe in future all users may put a star, not only participants of conversation.

In addition, there is a nice articles about DB normalization. It will really helps you to build well-organized DB structure: What is Normalisation (or Normalization)? http://www.studytonight.com/dbms/database-normalization.php http://searchsqlserver.techtarget.com/definition/normalization

Upvotes: 1

sheplu
sheplu

Reputation: 2975

depend on your application and how many users you will have. About the starred, archived and other stuff where both users can do things, you can use an enumeration or simply a couple of values. Not just a boolean. Or you can split every read with a senderRead and recipentRead

Upvotes: 0

Related Questions