0

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.

0

1 Answer 1

2

The preferred method is to use an intermediate table to just intersect friends and hikes. Then join through that table to list off friends who have gone on which hikes.

I've included a sample below as well as a sample query that shows each hike and the friends that have gone on that with you.

CREATE TABLE dbo.Hikes
    (
    HikeID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , LocationID INT NOT NULL
    , Summit NVARCHAR(100) NULL
    , DateStart DATETIME2 NOT NULL
    , DateEnd DATETIME2 NOT NULL
    )

CREATE TABLE dbo.Friend
    (
    FriendID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , FullName NVARCHAR(100) NULL
    , IsRealFriend BIT NULL DEFAULT 0
    )

CREATE TABLE dbo.HikeFriend
    (
    HikeFriendID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , HikeID INT NOT NULL REFERENCES dbo.Hikes (HikeID)
    , FriendID INT NOT NULL REFERENCES dbo.Friend (FriendID)
    )

CREATE UNIQUE INDEX IDXU_HikeFriend ON dbo.HikeFriend (HikeID, FriendID)

--Sample Query
SELECT H.LocationID
    , H.Summit
    , H.DateStart
    , H.DateEnd
    , F.FullName
FROM dbo.Hikes AS H
    LEFT OUTER JOIN dbo.HikeFriend AS HF ON HF.HikeID = H.HikeID
    LEFT OUTER JOIN dbo.Friend AS F ON F.FriendID = HF.FriendID
WHERE H.Summit = 'Kilimanjaro'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.