Hello database experts!
I'm designing a small database and ran into a situation where two foreign keys are in the same table referring to different rows in the parent table according to different actions. Let me explain.
The most typical examples of
N:M relationships are students enrolling in classes, products being tagged, tenants renting apartments, etc. But all of them describe just one action (enrolling, tagging, renting, etc) so what happens when two different actions are performed on the same child table? For example, a message being sent and received.
Let's say that we have two tables,
messages and we have the following business logic:
One user can send AND receive many messages.
One message is sent by a user and received by another.
In consequence, the
message table will have 2 foreign keys,
receiver_id, both users.
Here is where I get confused. We can create a joining table with foreign keys like
message_id but messages sent by user X and messages received by user Y will be saved all in the same table and I don't know if that's correct (not focused table).
Should we keep the two foreign keys in the
I searched before posting this question and I've got two links
Here people say that we should create joining tables for
N:M relationships but again, examples with one action being performed.
And here people seem to be Ok with the idea of two foreign keys for a child table.
What is the correct way to design a relationship like this?