1

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:

  • id
  • senderID
  • recipientID
  • timestamp
  • message
  • read (Boolean to record whether message has been read by recipient)
  • starred (Boolean to record whether message has been starred by recipient)
  • archived (Boolean to record whether message has been archived by recipient)
  • deleted (Boolean to record whether message has been deleted by recipient)
  • convoID

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?

M Smith
  • 430
  • 1
  • 7
  • 19

2 Answers2

1

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

Akru Bas
  • 38
  • 8
0

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

sheplu
  • 2,937
  • 3
  • 24
  • 21