Bridge tables

Bridge tables are used to connect fact and dimension tables. Bridge tables are also called “join tables” in classic SQL.

• The bridge tables available are:

  • bridge_user_team

  • bridge_user_group

  • bridge_track_content

bridge_user_team

This table can connect a user to the team(s) of which they are a member. In this case, a bridge table is needed because a single user can simultaneously be a member of multiple teams.

This bridge table captures the many-to-many relationships between users and teams on the platform. A row in this table reflects one period of user membership in the team and the role they had during this period. A user can have multiple periods in a team (e.g., joining and leaving multiple times).

A row will contain the start and end timestamps of the user’s membership period in the team. If ended_at is null, it means that the user is currently still part of the team.

Field	Description
group_id	The unique group identifier
user_id	The unique user identifier
team_id	The unique team identifier
started_at	The timestamp when the user was added to the team
ended_at	The timestamp when the user was removed from the team, if applicable

bridge_user_group

This bridge table is designed to capture user memberships in the group. An entry in this table reflects one period of user membership in the group and the role they had during this period. A user can have multiple periods in a group (e.g., joining and leaving multiple times or being assigned a new role).

A record will contain the start and end timestamps of the user’s membership period in the group. If ended_at is null, it means that the user is currently still part of the group.

Field	Description
group_id	The unique group identifier
user_id	The unique user identifier
started_at	The timestamp when the user was added to the group
ended_at	The timestamp when the user was removed from the group, if applicable
role	The role of the user in that period.

bridge_track_content

This bridge table captures the content items of each track. For each track in dim_track (referenced with track_version_id), this table contains the content items that make up the track.

Each row is a content item on a given track, with content_id, content_type, and position providing more details. You can join with dim_content to get more metadata on each content item like content_title, technology, topic, etc.

Field	Description
group_id	The unique group identifier
track_version_id	The unique track version identifier to which the content item belongs. This is the foreign key to join with dim_track
content_id	The unique content identifier of the content item
content_type	The content item’s type (course, chapter, project, assessment, etc.)
position	The position of the content item within the track (0 indicates the first item)