- Understanding Relationship Tables 3:41
- Relational Data 5 questions
- Querying Multiple Tables with JOIN 4:02
- JOINing Tables 1 objective
- Understanding SQL Injections 5:21
- SQL Injection 1 question
- Preparing SQL Statements 6:25
- Using a Prepared Statement 3 objectives
- Adding a Secondary Query 4:30
- Fetching in a While Loop 7:38
- Fetching Many Relationships 1 objective
Preview
Video Player
00:00
00:00
Understanding Relationship Tables
3:41A relational database is all about relationships. When talking about relationships within a database, there are three types of relationship.
1. One-to-one
2. Many-to-one (also used One-to-many)
3. Many-to-many
[MUSIC]
0:00
There are two main database
models in use today.
0:04
Relational and no SQL.
0:07
If your data structure fits nicely
into tables and rows a relational
0:09
database will offer you robust and
easy interaction with your data.
0:14
If you find yourself
trying to store arrays or
0:19
Json objects, possibly even with
different fields for each object.
0:22
That's a good sign that you're
better off with a no SQL database.
0:27
Our media data is fairly standardized and
fits well into tables and rows.
0:31
So we'll be using a relational database.
0:37
Sequel-light for this project.
0:39
A relational database is
all about relationships.
0:42
When talking about relationships
within a database,
0:45
there are three types of relationships.
0:48
One to one, many to one and many to many.
0:51
Let’s see how these relate to our data.
0:55
Our main table is our media table.
0:58
It contains shared elements for all the
items in our catalog, our unique media ID,
1:00
title, image, format,
year, category and genre.
1:07
Book items have two extra fields,
publisher and ISBN.
1:12
There is a separate books table for
this extra information.
1:16
The books table has a media ID,
publisher and
1:19
ISBN We'll link the media_id
of the Media table
1:24
to the media_id of the Books table
in a one-to-one relationship.
1:28
One media item has one
extra book information.
1:33
But remember,
not all media items are books, so
1:36
there may not be anything in the Books
table for a particular item.
1:40
Genre ID in our media table
links to a genre table.
1:45
Which includes a genre ID and the genre.
1:49
This is a many to one relationship.
1:51
Many media items link to one genre.
1:54
In this project,
each item may only have one genre.
1:57
But you could change that relationship
to include multiple genres.
2:01
This would be a many to many relationship.
2:06
This is how the people work.
2:08
There are many people associated
with each of our media items.
2:11
We can't store them in our media table
without creating a lot of duplication.
2:15
Just like we've talked about with code.
2:19
Good database design keeps
duplication to a minimum.
2:21
For example, there are many actors or
2:25
stars in a movie and each of those actors
may play a role in many different movies.
2:28
We couldn't include the actor
information in the media table without
2:32
duplicating that actor's information for
multiple movies in the database.
2:37
Instead we put the actors in a table and
the movies in another.
2:42
This is known as a many
to many relationship,
2:47
because one movie may have many actors.
2:50
And one actor may have many movies.
2:52
To create this many to many relationship
we have a people table with the people id
2:56
and full name, then we use a third
joining table named media people.
3:02
This stores the relationship
between media and people.
3:07
People relate to a media
item with particular roles.
3:11
Such as authors of a book,
director, writers, and
3:14
stars of a movie, and artists in music.
3:18
So we also defined the role
within our Media_People table.
3:21
A person may be the director and
also star in the same movie.
3:25
Or they could be a musical
artist who also authored a book.
3:30
However these people
relate the media items
3:34
can be explained in our
Media_People joining table.
3:37