How to model a Messaging/Chatting system in C# with Entity Framework

Question

I have a simple question

I'm developping a chat system service but I came across something interesting.

I'm currently operating this way :

First table :

[Messages] :
SenderID(user),
RecipientID(user),
Content(string) 

Of course, everytime a user sends a message to the other user, I add it to the table. But I thought about the fact that If a table has a million lines, it would become a mess

So I thought about another way being :

First Table :

[Conversation]
ConversationID,
USER1(user),
USER2(user)

Second table :

[Messages] in which I have 
ConversationID,
Content(string) 

So basically, I'm asking, which configuration should I use?

Answer 1

The approach below should be able to sort you out. This is a good basis for both chat and messaging, where with chat you can poll recent messages from the client side and slap on an intuitive UI.

Message

Message {
  MessageId,
  FromId, -- Foreign key User.UserId
  ToId, -- Foreign key User.UserId
  Subject, 
  Content,
  Attachment, -- can be null or default to a 0
  DateReceived, -- can be null or default to 1901 or sumin'
  DateRead
  ...
}

User

User {
  UserId
  UserName
  ...
}

Queries

Inbox = Message where ToId = Me.UserId
Sent = Message where FromId = Me.UserId
Conversation = Group by Subject
Attachment = (Simple = Path to attachment file. || Better = DocumentId)

Attachment

Document {
  int DocumentId,
  int DocTypeId,
  virtual DocumentType DocumentType,
  string FileName,
  int UserId,
  string mimeType,
  float fileSize,
  string storagePath,
  int OrganizationId,
  string fileHash,
  string ipAddress,
  DateTime DateCreated = DateTime.Now;
}

Then you run into the issue with group chats. Do you send a message to each recipient of the group or do you create a single message that each recipient has access to?

But we're keeping it simple.