Hey guys, in this video, we will start looking at relational database design as it relates to relationships, foreign keys, and overall normalization. Now on the screen, you'll see a database that I developed some time in the past. And you would see that we have an entity relationship diagram, as well as a bunch of lines that depict relationships. Now, the database being depicted here is really an occluding database for an Instagram application. And you'll see that I have here the main table being users because without users there would be pretty much no Instagram. And then from the users table, you'll notice that there are a bunch of lines highlighted when I hover over that entity.

Alright, so each of those lines depict relationships and what you would call dependencies, that users are other tables. Have on the data found in the users table. Now before I start dissecting the databases design, I would like for us to discuss shortly what normalization is. Now simply put normalization is a process by which you eliminate redundancies and repetitions in your database. Now, of course, you would probably ask, okay, what would be redundancy or petition. And let's just take this database here that we have on screen and look at an example of photos.

Now, if anybody watching this video has ever used Instagram, then you would know that a photo has to be posted by a user. All right, and then a user can have many photos. Now we see here that you have a database for the users and the user would have signed up, they put in their username and their password and their email address and so on. And this is a rather scaled down version of that, but we can work with it for now. So a user would have put in their username and we would have taken that time. stomp off when this user created their account.

And then having registered, this user can go ahead and start posting one or many pictures. And the pictures or photos will be stored in this database where we store a URL, because instead of storing the actual image, we're just storing a path to the image that we've just, you know, pulled back when we're displaying it on the application. And you would notice that this row here or this entry here in this entity, user underscore ID has a kind of red thing beside it. And that's what is usually used to depict dependencies dependency or a foreign key. No, the scenario would be that every time somebody posts a picture that you would say, okay, new picture with a new ID, you would post the image URL, and then let's say my username would be t Williams then you would say t Williams added I posted another one then you have T Williams, and then you have a bunch of T Williams is and then If there's something wrong with the code, maybe it will be t Williams up top, and then t Williams one below, and then something could go wrong and the whole text t Williams would become kind of tedious to maintain.

All right. And so we we want a clean way to associate a photo being associated with a user. And then that is where normalization comes in. Because when you realize that in associating records in one table with a record in another table kind of gets messy because maybe you're repeating unnecessary data, or maybe after three, four times the data seems unnecessary to repeat, then you would normalize this database by taking all this seemingly repeating data or potentially repeating data, putting it in its own table with its own primary key and its own table, and then you just reference that primary key where necessary. So in college text, what do we have a user posting a picture instead of repeating the user's username every time because something could go wrong, and its string will just reference their ID. So when I, maybe I was the first person to ever sign up for Instagram, my ID would be user ID one, when I posted my first picture, it would be user photo ID one with user ID one.

Alright. And then I maybe post a picture, you know, down the line, and it's a 200 feature on Instagram, then the photo ID would be photo ID one, and then the user ID is still on. So if I look in photos for all of the pictures posted by me, I can just say give me back all photos where user ID I went through SELECT statements with our conditions on so on. So I could just say give me about select our Select star from photos where user onto Score ID is equal to one. And that will match back all of the users from this table with user ID one and as much them here and bring back all of the features for user ID one. All right.

So that's a nice, clean and easy way for us to just maintain this referential integrity. And I spoke about data integrity before in this course, that is basically just maintaining that your data is clean, and your data makes it easy for you to associate and navigate across different tables. So in doing normalization, essentially, what you're doing is establishing foreign keys between tables. Because when you abstract the user information from your photos table, or maybe the photos information from the users table, because maybe your design initially had you trying to associate the photo with the the user in the same table, then you realize that you have to be repeating the user name in the users table when Your users table should really only have one instance of a user. So then what you would do is extract all the repeating filter information and put it in its own table.

So in the process of normalization, you know, it's kind of a break fix cycle, you get it right. You may get it wrong initially, and then you correct it later on. But then, for the remainder of this section, I'll just be showing you how you can go about doing it right the first time to reduce that break, fix cycle and that repetition, procedure process. If you if you read up on normalization online, you'll see a number of scholarly documents talking about the first normal form and the second normal form. And that's really useful for when you get to initially badly designed database but then as the designer, I put the onus on you or the responsibility on you to just do it right the first time. So if we take a closer look at the other tables here, We see that we have lines that depict which table is related to which and in the context of my SQL er D representation, you notice that you have kind of dotted lines versus solid lines.

So the solid line means that the ID or the foreign keys not is not optional in the related table, whereas the dotted line means it may not be necessary. So as the designer, you can set that up. So I can tell you right now that this was poorly designed, because I'm here seeing that photo can exist without the user. And that's wrong, because the foreign key from the user table to the full dose table is user ID. And then if you hover over the line, it will show you what the primary key is on one side and the foreign keys on the other side. And then the dotted line means that the foreign key might be empty, which is wrong because Instagram really should not have an image unless it has a user to associate with this image.

So that's wrong. However, on the other side between users and likes, if you hover over that solid line, then you'll see that the user ID on the likes side is the foreign key, which is associated with the ID in the user stable. And it's solid because you can have somebody liking something without a user. Well, you can't have a life without a user. And in the same way, I like only applies for photo. So we have likes being associated with photos.

And then this is what you call a link or table because it is storing two foreign keys. It has no primary key for itself what it is saying that this user depicted by user ID here, like this photo, so it is it is storing the foreign key for the user table and the foreign key for the full table. So in a nutshell, that's what a foreign key does the foreign keys pretty much just storing the value of a primary key from another table and then That brings up another thing when it relates to integrity because you can't or shouldn't, under any circumstance have a foreign key in a table that does not match back to a primary key in another table. All right, so you can have use it, you can't have 10 users in the user table numbered one through 10. And then have user number 11.

Liking photo number 250, when there are only 200 photos and 10 users, right. So that's another aspect of the foreign keys where the values of the foreign keys must match back to the value was in whatever primary key column is being referenced in the other table. And finally, once you establish that foreign key presence in another table, then you would have created what is called a relationship. So just by creating this foreign key for user ID here and user ID here. And if I hover over users, you'll see All of those highlighted lines, those are all the tables that depend on the primary key values stored in the users table, just by setting up that foreign key user underscore ID in any other table, then you would have established what we call a relationship. So for the remainder of this section, we will be going through modifying an existing table and adding foreign keys because for our school dB, we would have had multiple tables.

And I'm sure just by looking at it, you'd be wondering, how do we associate the teachers with the students and also we will be looking at doing that using the regular editor view versus the model design and creation view. So stick around exciting times are ahead