locked
Basic group chat database design RRS feed

  • Question

  • User-745958333 posted

    Any opinions on this database design for a basic group chat?  The only reason I ask is because I get this error when building this design using Entity Framework:

    Introducing FOREIGN KEY constraint 'FK_dbo.Message_dbo.AspNetUsers_AuthorId' on table 'Message' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

    I'm not quite sure what it's referring to?  I thought if I deleted the User, then the ConversationMember and Message would both be deleted too?

    USER

    UserID

    Username

    CONVERSATION

    ConversationID

    CONVERSATIONMEMBERS

    ConversationID (FK to Conversation.ConversationID)

    UserID (FK to User.UserID)

    MESSAGE

    MessageID

    ConversationID (FK to Conversation.ConversationID)

    AuthorID (FK to User.UserID)

    Message

    Sunday, November 22, 2015 2:23 PM

Answers

  • User1644755831 posted

    Hello Captain Planet,

    >>Introducing FOREIGN KEY constraint 'FK_dbo.Message_dbo.AspNetUsers_AuthorId' on table 'Message' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

    >>I'm not quite sure what it's referring to?  I thought if I deleted the User, then the ConversationMember and Message would both be deleted too?

    Yes if you use casecade delete on when you delete the user it will delete foreign reference paths. but in your case it has cycles because the message table contains AutherID. you don't need that. you can go to conversation and from there get the userid. you can remove the author id from the message table it should solve your issue.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 26, 2015 1:57 AM