Like other have said, you can store the table types with the ids.
But here's the neat part. That's often only for safety.
If I want to find all users and their orders, I can join the user table to the relationship table, then join that to the order table. Something like:
SELECT <COLUMNS GO HERE>
FROM
USER
INNER JOIN RELATIONSHIP ON USER.Id IN (RELATIONSHIP.LeftId, RELATIONSHIP.RightId)
INNER JOIN ORDER ON ORDER.Id IN (RELATIONSHIP.LeftId, RELATIONSHIP.RightId)
So you're getting the Users. The filtering the ones that have relationships, then filtering that to the ones that also have orders.
If you've ever managed an N:N relationship table, it's like that, but more generic.
But here's the neat part. That's often only for safety.
If I want to find all users and their orders, I can join the user table to the relationship table, then join that to the order table. Something like:
SELECT <COLUMNS GO HERE> FROM USER INNER JOIN RELATIONSHIP ON USER.Id IN (RELATIONSHIP.LeftId, RELATIONSHIP.RightId) INNER JOIN ORDER ON ORDER.Id IN (RELATIONSHIP.LeftId, RELATIONSHIP.RightId)
So you're getting the Users. The filtering the ones that have relationships, then filtering that to the ones that also have orders.
If you've ever managed an N:N relationship table, it's like that, but more generic.