What does a table with only 2 foreign keys do?

Question

I've researched about MySQL and I've come across this interesting ERD. I understand all of it except the user_chat table here. It has 2 foreign keys referencing from table chat and user. What does that do anyway? Does that mean:

1. I have to INSERT INTO message, chat, user_chat on manual when there is a new chat?
2. I can only do SELECT statement with this user_chat table?
3. The user_chat cannot contain other columns like favorite in case people want to quickly access it?
4. Can I delete a row in user_chat table, since they are not unique?

I'm self-learning and still a beginner on this subject so it would mean a lot if this can be explained to help me understand it. Much appreciated!

Answer 1

There is a many-to-many relationship between user and chat, that is to say that one user can participate in multiple chats, and each chat can have multiple users participating.

This sort of relationship is impossible to represent directly using FOREIGN KEY constraints, which can only enforce a one-to-many relationship. To work around this, database designers use an "intersection entity" table like user_chat, which allows the many-to-many relationship to be reduced to a "one-to-many-to-one" relationship.

When you create a new chat you insert a record into user_chat for each participant, and for any new participants that join the chat in progress. The main use of this intersection in this example is to allow you to easily search for all participants in a chat (in order to notify them of new messages, for example), or to find all conversations in which a specific user has participated.