59: SQL Relationships

Our final exercise is going to cover the concept of relations in SQL. In technical terms every table is a relation, but we're going to be more specific and talk about tables that are connected to other tables in various ways.

One-to-Many (1:M)

A "relation" in SQL is a method of using id columns in tables to associate one table to another through a "one to many" or "many to many" relationship. In our ECB data we have a rate for each country, and a currency that rate applies to. We can say the following about this relationship between rate and currency:

"A Rate has one Currency, and a Currency has many Rate."

In our 2NF version of the ECB data, the first part is modeled by placing a currency_id in the rate table, so that each rate row has only one currency.id. This also implements the "Currency has many Rates" side since any query for a currency.id in rate.currency_id would pull up all the daily rates for that one currency.

One-to-Many in Python

I find it helps to understand these concepts if you see how they're typically implemented in Python. If I wanted to say, "Rate has one Currency" in Python I'd do this: