About many to many relation

[tu_bui_huu]

Hi friends

I have a difficult problem on creating many to many database,

As a event manager, I have (for example) 10 events a month (120 events a year) , so I create a table name: EVENTS

Our company has about 200 freelance employees , and a part of them may join to organize our events , So I create a table name: EMPLOYEES

In that case (many to many relation) I have to create a new table name: EVENT DETAILS

And so, if 100 employee organize each event, so the table EVENT DETAILS may have 100*100 = 10.000 rows , and I think it is too many rows …

And the other way, I may create a new table for each event include many field: employee, salary, vendor, client, … but in that case, I have to create 120 table a year … I think it’s not good idea …

So, My question: is that right solution of that problem
and can we have the better solution for that

Thank you all in advance !

[JonathanBowen]

HI @tu_bui_huu - I think the first base you describe is the right one - Employees and Events are connected by a join table, in this case Event Details. I’m not sure what you mean by “too many rows” - perhaps too many for the Airtable plan you are on? Putting this to one side, it is just the number of rows you need to associate the Employees and Events, so neither too many or too few.

This structure has the advantage of allowing you to record additional information against each employee/event record. For example, the number of hours they worked, whether they turned up or not, planned start and finish time. This information can be rolled up to the Employees or Events tables. Using “number of hours worked” as an example, you can then roll this up on the Events table to see the total hours for that event. You can roll up hours worked on the employees table to show total hours worked across many events.

As you say, a new table for each event isn’t a great idea as generally you should keep entity records of the same type in a single table.