There is one unique relationship that we need to understand before we start building the Data Model, that is when you have multiple instances of table A related to multiple instances of table B, let’s take an example to clear this mumbling 🙂
A Person attends multiple Events.
An Event has multiple People attending.
We have a table for Person/People and another table for Events, how do we log the attendance?
We could
Set the attendance on the Person record and link the Person record to the Event table, but this way, we related the Person to one event only, what if we want to relate the Person to another event? and a third event? do we keep creating more links?
Or set the person on the Event table as an attendee, but then, what if a second Person signs up, do we create a second link? a third and so on?
These 2 approaches above won’t work, and that is why we need a third table called Attendance, this Attendance table links to both tables Event and Person, and we get to create multiple rows/records in this table.
Example Setup
Table: Persons
Table: Events
Table: Event Attendance
See how the Event Attendance table helped us create multiple links without having to duplicate Persons nor Events.
Here is a reading of the Event Attendance table
John Doe attended 3 Events
Back-to-School Supply Drive Event had 3 attendees.
Amara Johnson attended only one Event.
Community Food Drive & Distribution Day had 2 attendees.
With this setup in place, CRMs and systems give you the ability to perform filters and groupings. Example: show me the list of Events John Doe attended