This post is actually a note for myself about relationships in SQL. Most of the time tables are related to others through relationships. The principle is to avoid non-atomic values (first normal form) and data duplication (second normal form). There are 3 relations ships: one-to-many, many-to-many and one-to-one.
We’ll use fake data. Imagine we want to watch movies at the cinema. Let’s suppose I’m interested in adventure movies but I have no idea what movies are worth watching. So I read the reviews of these movies, which is a score in 5 and chooses the one with the best score.
- a table for the movies called movies with the schema:
|3||The Lost World||105|
- a table for the genres called genres with the schema:
- table for the promotions called reviews:
Note that we create the relationship by creating a foreign key. The foreign key movie_id references the primary key id in the table movies.
(Note: the examples used in this post are directly taken from a course on CodeSchool, The Sequel to SQL)
One row in table A can relate to many rows in table B. One row in table B relates to only one row in table A.
In our example: one movie can have many reviews but a review adresses only one movie.
|The Lost World||5|
One row in table A can relate to many rows in table B. One row in table B can relate to many rows in table A. To define this relationship we need a table C (the junction table) whose columns will be foreign keys referencing the primary keys of tables A and B.
In this example: A movie can have many genres and a genre can be associated to many movies.
Now if we want to know the genres of the movies we have to use 2 joins:
|3||The Lost World||Fantasy|
One row in table A relates to only one row in table B. One row in table B relates to only one row in table A. The primary key in B is the foreign key references the primary key in A.
In our example, we can imagine that each movie has a given budget and gross. These information would be stored in the table information:
The important thing to notice here is that the primary key of this table (movie_id) is also the foreign key references id in the table movies.
If we want to have the movies and these information we use a join on these 2 tables:
|The Lost World||5000000||10000000|
What movie am I going to watch?
I want to watch the adventure movie with the best average review. First let’s create a view to determine the adventure movies:
Then we use join between the adventure movies and the reviews tables. We group by movie and we average the score of the reviews:
OK. It seems I’m going to watch