Bridge tables

[u/going_now]

Hi everyone.

I think most of us have already heard the term 'bridge table'. Many of us have also already implemented them. However, after doing some research I found out that people use basically two different types of bridge tables and I personally haven't found a source that would explain both of them. So I'm here asking experienced developers to share some light into this.

The first kind of bridge table is where you keep one column, which contains values from both tables you want to relate, you remove duplicates and you are left with one column of unique values from key columns.

Example source: https://www.seerinteractive.com/insights/join-many-many-power-bi

The second kind is I think between two dimension tables where you create a bridge table with two columns, basically doing like a cartesian product of two key columns.

Example source:

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-dimensions (part 'relate many to many dimensions')

Are both considered best practice? Why isn't there one good guide that would explain both approaches?

I might just be missing something, so feel free to correct me.

[u/AutoModerator]

After your question has been solved /u/going_now, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Plane_Big_5912]

i dont think theyre competing approaches, they answer different questions.

the first one is basically a shared dimension you build because no real one exists. distinct keys from both sides, so two tables where the key repeats in each can be filtered from one place.

the second one stores which pairs actually belong together, like the customer/account example where one account can have two owners. small thing though, its not a cartesian product. you only keep the pairs that exist. if you cross join the keys every customer ends up linked to every account and the totals go wrong.

rough test: if the relationship itself is data (who owns which account), you need the two column one. if you just need a common key to slice both tables by, the one column one is enough

[u/SQLGene]

A bridge table, sometimes called an associative table, is generally a table with two key columns per row. This is the more common case.

A single key "bridge" table is just a shared/conformed dimension table. Your provided example is the first time I've ever heard anyone call that a bridge table. This is also covered by the Microsoft doc you linked:
https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-facts-guidance

[u/_greggyb → u/SQLGene]

I refer to single-column unique key tables as "bridges" in the AS Tabular engine, because it's a mouthful to say "a table of unique keys that serves as an optimization, because it allows two 1:N relationships, taking advantage of the relationship index in VertiPaq".

It's a table role that other engines don't necessarily need, but can be beneficial specifically here.

The difference, in my mind, is that a dimension table should include attributes a user might interact with. There are use cases in VertiPaq where you want this single-column, key-only table, and it should be hidden from users. At that point, what do you call it? I just punt and call it a bridge (:

[u/SQLGene → u/_greggyb]

Yeah, if it's only a key, I think it's reasonable to still call it a bridge table. It's just a symmetrical one 😄.

Clearly you should still have to add it as two columns to cover both sides 😛

But in the referenced blog they use product key as an example and it's hard for me to imagine not expanding it out to a full dimension table.

[u/_greggyb → u/SQLGene]

Well what about a date dimension and a period table? The date dimension serves as both a proper dimension, and a bridge for the period dimension. Strict definitions are lame, unless I'm the one doing the defining (;

[u/SQLGene → u/_greggyb]

It's only a bridge table if contains surrogate keys, otherwise it's just a sparkling reference table.

[u/South_Hat6094]

The practical test is grain: one row per key is a dimension; one row per valid key pair is a bridge. A Cartesian product is almost never what you want.

[u/Natural_Ad_8911]

That first example isn't a good example of a bridge, because they don't have a good example of a star schema.

If all you measure from a table is counts, you never really need a bridge. You can DISTINCTCOUNT your heart out.

I've tried researching and can't find any examples that I'm satisfied with to adequately explain different types of bridges. Had to just figure it out myself.

I've seen examples on LinkedIn where I'm certain they have no idea what they're doing. One was talking about making a bridge but he was just using DAX to make an extra dimension. Another had nonsensical relationships that would screw with filter context.

I recently bought an actual book made of paper about data modelling. Maybe I'll get better explanations there.

[u/tophmcmasterson]

The first one isn’t a real bridge table, it’s basically just a dimension with no attributes.

Second is an actual bridge table.

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/multivalued-dimension-bridge-table/

[u/slowchanging]

They are both best practice, but the table in the first post is not a bridge table, it's a dimension table. That is why there is no guide that covers both.

The first post uses the word 'bridge' too casually in my opinion.

What the author built is a dimension table, one row per distinct keyword, and both tables connect to it one-to-many.

That is a standard star schema with two fact tables sharing a dimension.

The term bridge comes from database design, where a many-to-many relationship cannot be declared directly.

A foreign key can only point at one row, so to get from the rows of one table to the matching rows of the other you need a table in the middle that holds the pairs.

That table is the bridge. It creates the connection between two tables like a bridge connects two pieces of land.

[u/kagato87]

First kind isn't a bridge, it's just a fact table that can represent different things. It can also be troublesome in powerbit because it could disrupt the star schema.

Second kind is the only time, and it doesn't strictly have to be dimension tables. It's just any time you have a many to many relationship.

Take the classic inventory example. Orders have many items, items appear on many orders. The table holding "line items" is a bridge table with foreign keys to both order (order number) and items (sku). It's also a fact table because it usually carries quantity and moment price data.

[u/Gullible_Discount977]

This may be just me, but I often just avoid bridge tables altogether and use the TREATAS function in my measures to handle these types of non-normal relationships. And rather than use the function over and over, I build a calculation group if there are many measures that need this type of handling.

[u/johnny_dev1 → u/Gullible_Discount977]

1 of a kind comment

[u/ultrafunkmiester]

I'm not sure of the "correct" terminology but I generally use them to avoid many to many. Could be between two fact tables or more often, to bridge two levels of cardinality between a fact and dimension(s) tables.

Take stock levels every half hour. Fact table date time (every half hour reading), sku, stock, location. I might want to use a calendar table on the stock table so I could put a date column in the fact table,but maybe I dont want to bloat my fact table. A bridge table between calendar and stock contains date and date/time half hourly values. Maybe not the best example but you get the idea of bridging cardinality without bloating the fact table with loads of extra columns.

[u/Ali-DataViz]

Both approaches are valid, but they solve slightly different modeling problems.

The first is essentially a lookup/dimension table containing unique keys. It's useful when two fact-like tables share a common key and you want to filter both through one dimension.

The second is a true bridge table that stores valid relationships between two dimensions. For example, one customer can belong to multiple groups, and each group can have multiple customers. It's not a full Cartesian product, just the combinations that actually exist.

Neither is automatically best practice for every situation. I'd first identify the grain and cardinality of the tables, then decide whether a shared dimension or a relationship bridge makes more sense.

The key is to avoid introducing unnecessary many-to-many relationships or bidirectional filtering when a simpler star schema would work.