This video is embedded from Youtube and here’s the original one.
Linking tables correctly is arguably the most difficult element of database architecture. The most typical connection is a direct link between two tables. However, this doesn’t always get the job done adequately. In cases where this approach falls short, we have a special table called a Junction Table to fall back on. A junction table is a special table that is created to specifically link two other tables together. This may sound redundant and unnecessary at first, but to illustrate the benefits, let’s imagine a specific scenario. In this video, we create a list of ‘Contractors’ and a list of ‘Hourly Rates’ – these are our first two tables. Sure, we can build a direct link between the tables, but as the video demonstrates, that doesn’t always produce the optimal solution, especially when we consider the development of those rates over time. For cases (like this) where a direct link between tables doesn’t suffice, you might consider turning to a junction table. A junction table will follow two specific rules: It will link to 2 or more tables The links to those tables will not permit links to multiple records per row In brief, consider using a junction table when you can’t find a direct solution to solve your architecture. This can be a handy weapon in your architecture arsenal when you’re looking to overcome specific hurdles!
Timestamp:
00:00 What this video covers
00:58 Framing the Example
01:15 Trying without a Junction Table
02:05 Why a Direct Link Fails
03:46 Trying a Rollup?
04:34 Introducing the Junction Table
05:09 2 Rules for a Junction Table
07:50 Key Takeaway