2 foreign keys in a many-to-many relationship

Question

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, users and 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, sender_id and receiver_id, both users.

Here is where I get confused. We can create a joining table with foreign keys like user_id and 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 messages table?

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?

Answer 1

... messages sent by user X and messages received by user Y will be saved all in the same table ...
Should we keep the two foreign keys in the messages table?

Short answer - Yes.

Every Message has a Sender, that must be valid User.
Every Message has a Receiver, that must be valid User.

Therefore, each of these fields requires a foreign key to the Users table, something like this:

create table table1 
( ... 
, sender_id integer ... 
, receiver_id integer ... 
... 
, foreign key ( sender_id ) references users ( id )
, foreign key ( receiver_id  ) references users ( id )
...
);