6

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.

Loa
  • 2,117
  • 3
  • 21
  • 45
kelua
  • 283
  • 1
  • 4
  • 9

3 Answers3

8

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.

Loa
  • 2,117
  • 3
  • 21
  • 45
0

I think you can use the second, but only with one many-to-many relation.

In this way, you can have a many-to-many relation with two foreign keys pointing USER and one for MESSAGE.

So, you have IDUSERSENDER, IDUSERRECEIVER and IDMESSAGE. I don't what you have in the message table, but you can also add an attribute in the relation called TEXT for insert here the conten of the message.

What do you think? Does this idea match your needs?

Piero Alberto
  • 3,823
  • 6
  • 56
  • 108
0

I would make sender_id an attribute of the message, while having a many-to-many table for recipients. Formally the ER model doesn't mix relationships between entities with attributes. I consider 2nd diagram formally correct even though the two relations will be implemented differently.

reaanb
  • 9,806
  • 2
  • 23
  • 37