This image represents a e-r diagram from a database I need to make, but I want to know if the relationsheep between fk_note and id (note table), and fk_users and id (users table) be made to get the id of each note when I do a select with inner joins.

CodePudding user response：

The "fk_note" and "fk_user" columns are both representing the same relationship. Having both is unnecessary, and a potential cause of errors.

If every user has only one note (or none), then:

• The "fk_note" column expresses the full relationship on its own
• The "fk_user" column can express the full relationship if it is also constrained to be Unique

In this case, the following two queries are equivalent - both return the single note for any user named "Rowan":

Select users.id, users.name, notes.id, notes.title
From users
Inner Join notes
   On notes.fk_user = users.id
Where
   users.name = 'Rowan'

and

Select users.id, users.name, notes.id, notes.title
From notes
Inner Join users 
   On users.fk_note = notes.id
Where
   users.name = 'Rowan'

If a user may have more than one note, then:

• The "fk_note" column cannot exist, because there is no single value for it to take
• The "fk_user" column expresses the full relationship

In this case, only the first query above (joining on notes.fk_user = users.id) is possible. It will return all the notes for any user named "Rowan".

If a user can have one "primary" and many "secondary" notes, then the "fk_note" column could represent the "primary note", and then having both columns would make sense (but choosing better names would be advisable).

The two queries above would then give different results:

• The first, joining on notes.fk_user = users.id would give all the secondary notes of the user.
• The second, joining on users.fk_note = notes.id would give only the primary note of the user.

CodePudding user response：

the relationship between notes id and fk_note is not necessary as the foreign key to user already marks the note as belonging to a specific user.

An easy way to ffind all user that belong to an not ist to run

SELECT n.*,u.* FROM notes n INNER JOIN users u ON n.fk_user = u.id