Search code examples
database-designerdentity-relationship

How to model messages exchanged between users? - ER Diagram

I'm trying to design an ER diagram where i have a USER and a MESSAGE entity. Users can send messages to other users. I have designed 2 alternative diagrams for this but i'm not sure which one is the correct. In the first one i use a recursive relationship with roles whereas in the second one, i use 2 different relationships.

Solution

  • Neither. Here we have a case of self-relationship.

    Sending messages is interpreted as an event, not as an entity, even if it has attributes/properties. There is a close connection here between two entities: users, and also users. The correct case for a conceptual model in an ER-Diagram would be:

    pic01

    Note the cardinality here. A user may or may not have messaged others. Likewise, a user may or may not receive messages from other users.

    Once you dismember it, that is, once you do the decomposition to the logical model, you end up with the following case:

    pic02

    "To" and "From" here represents a foreign key. This type of model results in two tables in the database. One will represent the users of the system. The other will represent the messages exchanged between users. Foreign keys here may or may not serve as a composite primary key. Me, in my opinion, I prefer the use of surrogate keys, but this is up to you.

    Hope I have helped in some way. If you have further questions, please comment and I will edit my answer, or I can add a comment too.