Determing relationship between comments and users SQL

Question

I am mostly a front end guy that is messing around with SQL, and I am having trouble determining whether the diagram I am making is a "Many to One" relationship, or a "One to One" relationship.

Table comments {
  id integer [pk]
  user_id integer
  text text
  post_url varchar
}

Table users {
  id integer [pk]
  user_name varchar
  email varchar
  password varchar
}

My current thought is that the comments table is a "Many To One" relationship with the user table. For example, many comments can belong to one user. Is this correct? Or is it a "One to One", seeing as one comment can only have on user.

A little confused with this stuff, so any input would be appreciated. Thank you.

You can think of this website now, everybody can create as many comments as they now. This website schema design can be found: https://meta.stackexchange.com/questions/2677/database-schema-documentation-for-the-public-data-dump-and-sede/326361#326361 — jian, Jul 27 '22 at 07:06

score 1 · Accepted Answer · answered Jul 27 '22 at 07:01

1

It's best to express a relationship in both directions:

Each comment belongs to one user
Each user can make 0 or more comments

This relationship would be seen as a many-to-one relationship. A one-to-one relationship means one-to-one in both directions, while a many-to-many relationship means one-to-many in both directions.

answered Jul 27 '22 at 07:01

reaanb

9,806
2
23
37

So when describing this to someone, If I was referring to the `users` table, I would say this has a "many to one" relationship with the `comments` table. Then if I was referring to the comments table, I would say that `comments` has a "one to many" relationship with `users`? Not sure exactly how to communicate this with someone else. – Dan Zuzevich Jul 27 '22 at 07:07
Hopefully that makes sense what I 'm asking. – Dan Zuzevich Jul 27 '22 at 07:08
Formally, relationships exist between entities (conceptual model), not between tables (physical model). In the relational model, tables don't represent entities but predicates in which entities participate. The relationship in your table design is represented by the pairing of columns `(id, user_id)` in the comment table, not by an FK constraint between the tables - FK constraints are only integrity constraints. – reaanb Jul 27 '22 at 09:45
1

Thus, I would say the comments table (physical model) represents a functional dependency `id -> user_id` (logical model) which represents a many-to-one relationship from comments to users (conceptual model). The relationship in the opposite direction is implied and doesn't need to be stated. – reaanb Jul 27 '22 at 09:45
I suggest looking at some of my older replies: https://stackoverflow.com/questions/38669331/how-to-determine-relationship-11-1n-nm-between-tables-when-reverse-engine/38673263#38673263 https://stackoverflow.com/questions/44050260/why-does-the-m-n-relationship-in-the-db-design-have-to-create-a-new-relation/44057979#44057979 https://stackoverflow.com/questions/42712751/whats-the-relationship-between-the-two-entities/42714033#42714033 – reaanb Jul 27 '22 at 10:16

score 0 · Answer 2 · answered Jul 27 '22 at 07:01

It is many-to-one relation from comments to users as you mentioned many comments MUST belong to one user and thats why you specify user_id in comments table. If the one-to-one is the case for comments and users, you have to specify comment_id in users table and this will cause always only one comment will be in your datum.

Determing relationship between comments and users SQL

2 Answers2