MySQL many to many table convention

Question

Assume that there is 2 tables users and events.

[ users ]
- id(PK)
- name

[ events ]
- id(PK)
- title
- description

Normally, pivot table(M:N) looks like this.

[ user_event ]
- id(PK)
- user_id(FK to users)
- event_id(FK to events)

Should I insert additional column to user_event m:n table?

[ user_event ]
- id(PK)
- user_id(FK to users)
- event_id(FK to events)
- is_leave

I have to check if user leave the event or not. So is_leave column is added to user_event.

I wonder that, is it a bad way?

Is there any convention here?

Thanks.

Linking tables often have columns that pertain to the two keys forming the link. For example, a table linking students and courses might have the grade that a particular student earned in a particular course. — Honeyboy Wilson, Jun 26 '20 at 01:40
This doesn't have an answer outside of a given information modelling method. What you "should" do is follow one & where stuck understanding or applying it ask a question about that while referencing it. — philipxy, Jun 26 '20 at 01:47

score 2 · Answer 1 · answered Jun 26 '20 at 03:57

2

Building on Bill's "intersection" discussion...

Get rid of the id it is useless and slows things down.
Provide composite indexes in both directions.

Details and more tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

answered Jun 26 '20 at 03:57

Rick James

135,179
13
127
222

score 1 · Accepted Answer · answered Jun 26 '20 at 01:40

It's fine to add attribute columns to user_event, which I would call the intersection table*.

The rule should be if the attribute pertains to an individual user attending a particular event, then it's correct to put it in the intersection table.

In other words, it doesn't belong in the users table, because it doesn't apply to all events the user has attended. It doesn't belong in the events table, because it doesn't apply to all users of the given event.

* There's no official terminology for this type of table. I prefer "intersection table" but I've heard other terms.

MySQL many to many table convention

2 Answers2