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