0

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?

learnprogramming
  • 159
  • 2
  • 9
  • What do you mean "Is there an efficient way for achieve that?" It looks like you already achieved what you wanted? – reaanb Aug 29 '19 at 11:34

1 Answers1

1

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.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Ahhh, thanks :). Now COMMENT turns around all. **About concepts** that I do not remember: **1.** Is "conceptual relationship" ER Diagram or business rules?. **2.** Is "Entity set" USER and CITY too? I say that for "set" word, I always say for example: Entidad USUARIO (in spanish) – learnprogramming Aug 30 '19 at 03:14
  • **About argue: 1.** About the identifier that I added to `user_comments_city` table. I do it most of the time and I do not turn it to entity set. I imagine a situation like this: I have an huge amount of flow per day in my website and this situation could happen: differents `id_user` (PK) comment something (`text`) in same `id_city` (PK) at same `date` (PK). In that situation for sort them I think that I could get random results, so I use `id_comment` for avoid that problem AND it ease me work with `user_comments_city` table (for example: sort comments). – learnprogramming Aug 30 '19 at 04:17
  • By "conceptual relationship" I meant that, in the conceptual model (i.e. the ER model), the relationship was turned into an entity set. USER and CITY are entity sets too. Entity sets (and value sets) are the ER concepts corresponding to domains in the relational model, i.e. they're sets of values. – reaanb Aug 30 '19 at 05:38
  • If COMMENT is identified by id_comment, then it's a set of values, i.e. an entity set. If it's identified by (id_user, id_city) then it's a relationship. If you don't follow that approach, you're deviating from the ER model as described by Chen. – reaanb Aug 30 '19 at 05:47
  • Your comments were useful :) . In your last comment... I proposed to my teacher years ago the situation that I told in my comment " **About argue** " and he told me something like "Ok, you could use `id` for the table as a trick." and after that I had it as relationship in my ER Diagram (what it could be his unknowledge). Then, I ask: Could I solve the problem that I said in " **About argue** " keeping the relationship in my ER Diagram? It is only for know it. – learnprogramming Aug 30 '19 at 18:02
  • I already explained my perspective. The ER model isn't the epitome of data modeling but if you're going to use it, understanding the fundamental concepts will make it more sensible and satisfying. I suggest you read [The Entity-Relationship Model - Toward a Unified View of Data](https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.523.6679) for more info. – reaanb Aug 30 '19 at 19:11
  • Thanks for the info, I will take a look because I have to refresh concepts. – learnprogramming Aug 31 '19 at 21:18