Many-to-many relationships
Another type of relationship between records is many-to-many. The relationship between siblings is many-to-many. I can have many brothers and sisters, as can they. But how can I store this information? A reference field can only point to one record.
Adding a lot of reference fields into a form is one way. I could create as many reference fields as I have siblings. However, that's not great design. What if another brother or sister were born? Making new fields every time a new baby arrives is not cool.
Instead, we could create another table that sits in between two records, acting as the "glue" that sticks them together. This many-to-many table has two reference fields, each pointing to a different side of the relationship.
In the hotel application, we want to take reservations for our guests. Each reservation might be for more than one person, and each person might have more than one reservation. This sounds like the perfect use for a many-to-many table. Here's a...
