Search code examples
database-designrelationshipentity-relationshipbusiness-rules

I created in an ER Diagram two relationships in only one. Can I do that?

First, I created the business rules:

Users can comment in cities, users comments can be answered by other users.

The comments hierarchy is: Root comments, answer to root comments, answer to answer root comments. So, the hierarchy has only one level.

Example about my idea:

Ana123: I was in Seville and I love it. (Root comment)
    Juan321: Ohh, yesss it's beautiful. (answer to root comments)
    Manuel99: "Juan321" Yessss :D (answer to answer root comments)

Then:

  • An user comments in zero or many cities.
  • A city is commented by zero or many users.
  • An user answers to zero or many users.
  • An user is answered by zero or many users.
  • User has: id, name, email, password.
  • City has: id, name, description.

From the bussiness rules I got this ER Diagram: ER Diagram

NOTE: I deleted the red relationship because is isolated from CITY and for me it has not much sense.

So from USER - CITY relationship I get the next table:

TABLE: user_comments_city
id_comment(PK) | id_user | id_city | text | date

But as I said I combined two relationships in only one, I added "answer_comment" field (with this field I think that I achieve that). I will use the example above:

TABLE: user_comments_city
id_comment(PK) | id_user | id_city | text                           | date              | answer_comment
...              ...       ...       ...                              ...                 ... (NOTE: "..." represents other comments)
15               2         4         I was in Seville and I love it.  20/08/2019 20:20
16               13        4         Ohh, yesss it's beautiful.       20/08/2019 21:31    15
...              ...       ...       ...                              ...
21               9         4         Yessss :D                        21/08/2019 11:20    16
22               17        4         I love it too :)                 21/08/2019 14:00    15

So, I ask: Can I do that? Is there an efficient way for achieve that?

Solution

  • Short answer: Yes, you can do that.

    Long answer: The surrogate identifier (id_comment) in the user_comments_city table indicates that the conceptual relationship has been changed to an entity set. A relationship is identified by the keys of the entity sets it relates. The corresponding ER diagram for your modified user_comments_city table looks like this:

    ER diagram for users, comments and cities

    Really, what you've done is not to combine two relationships into one, but to denormalize three relationships (user_comments, city_comments and answer_comments) into the entity relation for the COMMENT entity set.