Hey guys, congratulations for making it this far. Now, we move on to a new part of database and this aspect of database design and development usually people express a bit of angst and anxiety towards understanding and completely applying it. And to be fair, it takes a bit of understanding and practice to actually gather the concepts that are required for you to be proficient. So, this part of databases and has to do with relationships and foreign keys and the bigger elephant that is always in the room when this topic is being discussed is normalization. Now, let me start off with relationships. Relationships are essentially what what the word means they speak about how data is related.

Now, we will have started up the course by Seeing that we're using SQL server which is a relational database management engine or relational database, PostgreSQL my SQL, there are a few others on the market that are also relational. And there are some that are not so relational databases, they thrive on their ability to have relationships. And I keep on seeing that relationships. So let me explain. Relationships are essentially talking about how the data relies on each other. For instance, if you look on my screen, you'll see that I have what we call a database diagram or entity relationship diagram, which is showing entities in the tables in the forms of entities.

So a table once again, is the collection of different roles of different entities so that in database terms, the whole team is called an entity. All right. So this is a database I was working on outside of this course. But I'm just bringing this up. So we have a bigger picture of a completed and fully relation, fully normalized database. And so you see that each entity or table with all of its colors, right?

And then you see that there is something here is connecting it to another table. So you can see leaderboards is connected some way somehow to the users table. Predictions is connected to the users table. And so this visual representation of relationships can help us to, you know, conceptualize what purpose our relationship serves. So in a nutshell, it's literally saying that entities are connected to each other. All right, they share data, they share some dependency, you can't have one without the other artists.

It's very difficult to have one without the other. That's what this connection means. So we can have a user. And if you notice, the key is always going in one direction, right. So it is going from this leaderboards and the key is pointing to users, which means you can't have a leaderboard entity without some representation of a user. You can't have a prediction in the system without some representation of the user.

And the same for pretty much. So this database was being developed to be the storage mechanism for World Cup prediction application. All right, yes, I built one of those in the past. And so people could log in log a prediction. So of course, when they log the prediction, they needed to state which much it is that they were going to be predicting, making the prediction on then so they had to choose the much right? And then we would have to know who is making that prediction.

So of course, I had to keep You record off the user. And so that brings me to the second part of relationships, which is called the foreign keys. And, and this is all very high level. So of course, we'll be getting into more details as we go along. But I'm just giving you a high level representation of relationships on foreign keys. So a foreign key is essentially just the bits of data that you need to help you identify the related entity.

And that's all Spanish. Let me just show you in in practical terms, and then we can revisit that, that explanation. So let's stay on our predictions table. So we have a prediction. We all know that once we create a table and we're creating an entity, we need a primary key. And so that is depicted right here by this is the key emblem, and we would have gone to creating tables and setting primary keys and all those things.

So that shouldn't be foreign. And then we have a bunch of properties that we know we put on. And this doesn't display the data type, which is really immaterial in this setting in this world. So we have an idea for the prediction. And we know if it's an active prediction, or you know if it should be counted among the many. Any comment, that was the joke, Ray, just another property that was there if they spend the entire system for you to understand that, but that is not really important.

And then you'll see something much ID, okay. And if you notice, well, it's not always like this. I don't want you to necessarily take this as a rule of thumb, but sometimes you'll actually see this connection originating directly from the primary key or the foreign key rather. so in this situation, it doesn't so that's not really something to go by. But you'll see that we have much ID and much ID is the reference T shirt on off the much from our other table. So in other words, it would be difficult for us to have a prediction.

And we actually spell out the match. Because we're actually creating the matches here, we actually created a time table for all the matches that are going to occur. I was stored that in a table, it would be kind of foolish and redundant to keep on repeating all of these details, so you know which much the prediction is being created on. All right, so what we did was have a table just for matches. And then what we do is each match an ID, and then each time a prediction is made, we just reference the ID of the match that is being predicted. All right.

So let me say that again, we have a table solely for one entity, it has no dependencies or much can exist without having anything else. Well, in this case, I much needs our group. So you see that Much still needs to be associated with our group. And then the match can exist with other prediction. So the match table has all the matches. And then a prediction can only exist if a match is associated with it.

So that is that kind of association, you always have to map out what bits of data can exist without any other data, and then figure out what else what needs this pre existing data in order to actually survive. So let me use the users table since this one has absolutely no dependencies. So a user is a user user exists, right? I can create a user tomorrow. I don't need anything else. In this case, I don't need gender or anything.

So I just have a user who has either he's active, the data was created, he or she was created, and certain contact information. And that's all I need on a user. However, to make a prediction I have to see which user is making this prediction. So I can't, it would be foolish of me to create a prediction and write about the person's first name, last name, and username every time or just the username, because then I might have a spelling error over here. And the username is something over here, and then trying to match them back. That's going to cause problems.

And when we get into more advanced queries with Inner Joins, and so on, you'll understand what I'm saying. But what we do is we know that the ID is always absolute ID is always unique. So we want to reference this unique data points to make sure that we always know who is making a prediction. So we create the predictions table and we give the prediction all of its properties that it needs. But then we say okay, for who is making the prediction, we just want to reference that person by ID. So that user ID here much as the primary key values So if the user is one here, then the expectation is that when user one makes a prediction, all of this gets filled in.

And one would be the value here when user 10 makes a prediction. Everything else gets filled in. And 10 is the value here. So if we look at who made the prediction, we see user ID 10, I can go back to my users table, and I see 10. And I see Oh, Trevor Williams created that prediction. So that is the relevance of a foreign key, the foreign key cuts down all of the repetition of data across the tables.

And allows you too, have a unique reference points between two tables, and whatever data needs to be shared amongst them. All right. So that's as the example with predictions and users. So in the same way with the matches, the prediction can only exist on a much so instead of saying, oh, where where make a prediction on TV Want to Team tool and then somebody comes along and makes the same prediction on Team one and team two. And then we repeat that again for that entry, then we say this prediction exists on much ID five. If we go to our matches table, we can see match ID five and get all the details here.

So we don't really have to repeat the details across the tables. And that principle is also called normalization, where you're looking at repeating data and reducing it. So that is why this video is called relationships, normalization and foreign keys because they all go hand in hand with each other in reducing redundancy and repetition, again, across tables. So pretty much the only repetition you should have would be your foreign key values. Because if user 10 makes 10 predictions, then you'll see 10 predictions in the database. Having that repeated ID over the details of user can only appear once and that's it, one place that we can go to.

Alright. And that is essentially in a nutshell that is how foreign keys normalization, and relationships work. For the rest of this section we will actually be looking at creating tables and creating relationships and we will build on the database that we have already started and we will start looking into more advanced database concepts. See you soon