Understanding Relationship Tables3:41 with Alena Holligan
A relational database is all about relationships. When talking about relationships within a database, there are three types of relationship.
2. Many-to-one (also used One-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.
[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