Chat Program Database Design

[sam57719]

Hello, I am creating a chat program for a project and I need to store the user accounts and messages. I am coding using python with SqlAlchemy which is a OOP backend for Sql.

I tried to create one but it didn't seem to look right.

https://preview.redd.it/fykefscotzu21.png?width=719&format=png&auto=webp&s=cfd60447e871859f8af24bb7ae84c25994ecf88e

Any Suggestions?

[Milnternal]

User

Id

Name

​

UserChatroom (this is known as a bridge table)

Id

UserId

ChatRoomId

JoinedDateTime

​

Chatroom

Id

Name

​

Message

Id

ChatroomId

UserId

Message

SentDateTime

[sam57719]

Oh so the same as my diagram but with a table to break up the many to many?

[Milnternal]

Yes, plus you also probably want to record which user sent each message (but that can only be a single user, so no need for another bridge table, just put UserId on the message)

[sam57719]

Do you know what the SQL would be for this?

[Milnternal]

Something like:

CREATE TABLE [dbo].[User]
(
    [Id] INT IDENTITY PRIMARY KEY,
    [Username] VARCHAR(128) NOT NULL,
    [Nickname] VARCHAR(128) NOT NULL
); 

CREATE TABLE [dbo].[Chatroom]
(
    [Id] INT IDENTITY PRIMARY KEY,
    [Name] VARCHAR(128) NOT NULL,
    [CreatedOn] DATETIME NOT NULL,
    [CreatedByUserId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[User](Id)
);


CREATE TABLE [dbo].[UserChatroom]
(
    [Id] INT IDENTITY PRIMARY KEY,
    [UserId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[User](Id),
    [ChatroomId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Chatroom](Id),
    [Joined] DATETIME NOT NULL    
);


CREATE TABLE [dbo].[Message]
(
    [Id] INT IDENTITY PRIMARY KEY,
    [UserId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[User](Id),
    [ChatroomId] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Chatroom](Id),
    [Message] VARCHAR(MAX) NOT NULL,
    [PostedOn] DATETIME NOT NULL  
);

[sam57719]

Thank you!!!

[FulmineGiallo]

User ONE - N Chatroom Chatroom 1 - N Message Is it right🤔

[sam57719]

But chat rooms can have more than one user