I'm designing a database that tracks hikes I have gone on. There is a Hikes table I have columns that include HikeID, LocationID, Type, Summit, #Friends. I then have a table called Friends which documents friends I've gone on hikes with.
What is the best way to document which friends have gone on which hikes without a host of null values?
My current idea is to create an intermediary table called FriendCombinations with a number of columns equal to the most friends I'd expect to ever hike with and then have an ID row in this table to reference in the Hikes table. Obviously this will produce null values but I'd rather have those be in the FriendCombinations table rather than the hikes table.
Is there a simpler and more elegant solution I'm just not realizing? Thanks.