Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Integrating PHP with Databases

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Understanding Relationship Tables

3:41 with Alena Holligan

A 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

Learn More About Relational Databases

A many-to-one relationship can also be referred to as a one-to-many depending on how you are referencing the relationship. Genre_id in our Media table links to a genre table which includes a genre_id and the genre. This is a Many-to-one relationship. Many media items link to one genre. We could also say that One genre has Many media items using a One-to-Many relationship.

https://teamtreehouse.com/library/database-foundations

https://teamtreehouse.com/library/sql-basics

Database Diagram

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    [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

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up