Database Relationships | Caleb Curry | Skillshare
Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
20 Lessons (1h 42m)
    • 1. Entities and Attributes

      3:16
    • 2. Relations (Tables)

      5:31
    • 3. Data Integrity

      4:46
    • 4. Atomicity

      1:24
    • 5. Primary and Foreign Keys

      4:25
    • 6. What is a Relationship

      3:02
    • 7. Conceptual vs Physical Relationships

      2:12
    • 8. One-to-One Relationships (Conceptual)

      3:28
    • 9. One-to-Many Relationships (Conceptual)

      4:27
    • 10. Many-to-Many Relationships (Conceptual)

      2:39
    • 11. Sometimes You Decide on the Relationship

      2:38
    • 12. Referential Integrity

      9:51
    • 13. Parent and Child Tables

      5:13
    • 14. NOT NULL

      5:46
    • 15. How to Structure One-to-One Relationships

      9:03
    • 16. One to One Examples

      6:06
    • 17. How to Structure One-to-Many Relationships

      8:13
    • 18. One-to-Many Examples

      6:16
    • 19. How to Structure Many-to-Many Relationships

      6:07
    • 20. Many-to-Many Examples

      7:07

About This Class

The hardest part about databases is that there is a huge mountain of knowledge needed before even beginning to understand how to design and create them. It doesn't have to be this way. This course will take you from knowing nothing about databases to being able to decisively design relationships between tables. Now, if you're a beginner, you might be asking..."What are database relationships and why do they matter?" The answer is simple, every relational database is built using the fundamentals of database relationships.

If you want to really understand what a database is, how it works, and how to design your very first set of database tables, this course is for you.

Every lecture is taught in an easy to follow method using on-screen illustrations, drawings, and examples. This course will bring you to a level of understanding that will allow you to develop skills quickly and use them at home, in school, and even in the work setting. These are practical skills. The skills learned in this course are skills that can help you get a job in IT or Database design.

Do you want even better news? These skills are easy. No more confusion, no more fright, no more being a step behind everybody else in database technology. Here is just some of the information you will learn in this course:

  • The basics of databases - what they are, how they work, and how they structure data.
  • Data management - How do we keep millions of pieces of information organized and up-to-date?
  • Data structure - How do we organize data so that our database is protected from anomalies?
  • How to structure the three types of relationships between tables - one-to-one, one-to-many, and many-to-many. Each with practical examples.

I didn't create this course to sell you something that's going to waste your time. I created this course because I have a passion for databases and I love teaching in a fun way. This course is fun, easy to follow, and worth every penny. 

Transcripts

1. Entities and Attributes: Hello, everybody In this video, I want to give you an introduction to databases. That's because I want you to have a foundation to build upon once we start talking about database relationships. So to begin, let me give you an introduction by saying what a database is. Well, a database stores data. You can see I drew this database kind as a barrel, and a bunch of data is being chucked or thrown into the database. So yeah, that's what a database does. Holds data. Well, what's data data Could be anything I personally like to think of data, just his information information. That's a very broad term. And you should think of data very broadly because it could be anything, anything you'd like to store record document that can all be put within a database. But we don't just throw it in a database for it to be all thrown in there unorganized. We structure the database in a way that we throw all this data in which might be millions and millions of pieces of data, and we get out and organized, structured result, and it might be on a Web page or something else. Anything we really want to do so. There's a couple terms you should know about when we're talking about databases and that's entity and attributes. Entity is anything we want to store data about In the attributes are the things we store about the entity. We can apply this to a practical example by looking at a person, well, a person being the entity. It has a name, phone number, membership status in a favorite color. These are just examples of attributes about a person you can also see. This is a person. I'm not giving you a specific example of a person, but in reality you could. You could replace this picture here with a picture of somebody, and you could put in their name, their phone number, their membership status, their favorite color. And then you can kind of use this whole thing as a blueprint and fill in data to get something else. And it would look something like this so you can see here this losers the entity. And here are his attributes we have is User Id's first name, last name, phone number, membership status and favorite color said these attributes describe the specific entity. Now what What else could an entity be? Well, in entities, not always a person. In fact, it's not even always something that is concrete that you can see. We could even take this membership status, which is kind of like a concept rather than something you can feel. And we could use that as its own entity and give it its own attributes. So now if you look, we have attributes about the entity. Here are the attributes we have membership status, which is an i d. Basically a description and a price, which all describes the membership status. So in conclusion, we have an entity and attributes a database is just a collection of entities and attributes describing those entities. So thank you guys for watching Now see you in the next video. 2. Relations (Tables): Hello, everybody welcome. And in this video, we're going to be discussing database relations. Relations are displayed as tables. Each entity type is going to get their own table so you can see here we have a membership user and review. That's because every single user is going to be within this user table. So it's a type of our data. So this is our entity type. And if you remember from the last video, we had this illustration here where we had a person, which was the entity and all of the attributes for that person, well, it's kind of the same way up here. It's just that it's organized in a table. So now we have the user and all of the attributes for that user, the reviews and all the attributes for the the reviews and same for membership, the membership and all the attributes for the membership. It's a little confusing, so if we look at this example, we had the person and we kind of illustrate that here in the user's table, where the user table except I didn't label it person. I labeled it user because I think that's a little more practical in this situation I gave him on I D and I have their membership and then also have their first and last name, their phone number, email and user name. So this is ah way we could take this blueprint that we talked about earlier and put it into a table. Now what if you want to put specific data such as an individual person like, let's say we have this guy and he's happy's walking down the park. Yeah, screaming woo. Well, we need to put his attributes so we could say his user I d and so forth. Well, that is all go within this table. So basically, we took these attributes and we set them as the headers for this table. And then we can fill in data. So is use your i d. Which we don't really know. It might be 608. That system number I made up out of my head. I didn't get that from anywhere. Membership I d. That might be too. First name might be Caleb Curry, Yadi, Yadi, Yadi, yada, yada and so forth. And you can fill in that data. Now. We also have room for more people. So we could have another user, which would be 609 and his membership status might also be, too. And then his first name might be Caleb, and his last name could be, Ah, flashlight eyes. The first thing I could think of and his phone number could be something this email and its user name and so forth. So now we take individual entities, and we put them within our table. The table is three user table, so the table name user describes what each entity within our table is. So 608 is a user. 609 is a user. I should probably is more colors to try. This 609 is also user. I think I just made it messier, but it's fine. You guys get the point now when we take all of the information from one person, such as all this information here we're describing one entity were describing the user with the I d of 609. His membership I D is to his first name's Caleb. His last name is flashlight in his phone number, email and user name. All of this right here is known as a row a row, describes all of the information about one individual specific entities, such as the user, with the idea of 609. A column, on the other hand, takes all of the values four and attributes. Here we have all of the attributes for the membership i d. Attributes category, and that is known as a column. This whole thing is known as a table, and if you want a little bit more detail, an individual value basically where a row and a column meats this data within that square is known as a value. Did just summarize these terms and give you a couple more? You might see you have this table that might be useful for you. A relation, which is the table is also known as a table with stores and organizes all of our information. It's also known as a file a rotary talked about. It's also known as a record and a tough or to pull and attributes is stored within a column , and it's also known as a field. So these air just a couple more terms. Look at him across this way, so all of these are referring to basically the same thing. All these air basically record referring to the same thing. And finally all of these air basically referring to the same thing. So once you understand a little bit of how our data is presented within our database, you will understand what we need to do to create the best relationships. So, thank you guys, and I will catch you in the next video. 3. Data Integrity: Hello, everybody. In this video, we will be discussing data integrity. When we design a database we design in a way that it structured. It protects our data, protects it from being deleted when it's not supposed to be. If we're text from duplicates, incorrect data, conflicting data and also protects it from unorganized data. Often, database management systems have some sort of rules that protect our data. But we can add our own by programming them in. Now. This video isn't going to necessarily go into the programming because this is about the design concepts. I'm just scratching the bare, minimal surface of the soul. There are three main types of data integrity. We have entity integrity, referential integrity and domain integrity. You can remember that because the first letter of each word is e R D. Which is also the same acronym for entity relationship diagram. And if you don't know an entity, relationship diagram is This is a good example. It's when we represent our tables in diagrams Entity integrity is important because it protects our individual entities. We talked about entities in our last couple of videos. You can see in this example we have three entities, they're of people, and they all conveniently are named James Smith. So this brings up the question. Are these three people actually three people? Because if you take a look, this guy James Smith and the other James Smith below him all have the same exact data. You know, it states in the States. Texas Houston, December 12 James Smith. They have repeating data. So then that brings us to question. Is this the same guy we don't really know? Is this to James Smith's who just conveniently were born on the same day and live in the same city, which in reality really possible? Because James Smith is one of the most common names ever, and the other stuff is really not that hard to do. I mean, you could easily live in the same town as somebody, and you could easily be born on the same day as somebody. So I really wouldn't be surprised if this this is actually two people. But it could have very it could very well be the same person in the database two times that that's bad. So what we need to do is implement entity integrity. Now let's look at the last guy. It's James Smith and he's from New York, New York. Well, this brings up another question. Maybe this. These two James Smiths are actually the same guy, and then the same guy moved to New York, and then he had to update his information. So we put another entry in the database, which brings him a total of three rows. See how that's is really overall confusing. That's just bad. The other one is referential integrity. When you look at referential integrity, it's talking about the connection between tables. If you look at these tables, you can see that we have a user. I d. We also have a user I d. Over here. There is some kind of connection here, which is represented by this bar. This is known as a foreign key connection, and we will be getting into those a little bit more in the future. But for now, just understand that these are connected because when you have a review, you must have a user who put the review because if you don't well, then that review just came out of nowhere. So there is a connection between these two user ID's that's going to go on an individual basis like the user idea of six might be connected with the user idea of six in this table . The last form of integrity is domain integrity. Domain integrity is when we try to have all of the same information within one column. For example, if we have a phone number column, all of the rows should have the same kind of data for that column. They should all be a 10 digits, and they should all be numbers. Now if someone's in there and they're just writing random stuff like Elwell pie or my name is 12 well, that doesn't make any sense, and that doesn't belong there. That's usually enforced with something known as a data type. And you can also add extra programming languages on top of your database to ensure that at a further level, for example, websites, you can try to put your phone number in. And then whatever the website is programmed with such as PHP can be like no, or maybe jobless script could be like, No, that's not a phone number. That's an example of all three forms off data integrity, and I'll see you all in the next video 4. Atomicity: Hello, everybody. In this video, I will talk about things being atomic when I say things are atomic, I mean, they are in their smallest piece possible. It's not consisted of multiple little pieces. Good example of this is a name. Well, a name consists of a first name, maybe a middle name or a middle initial in a last name and possibly some titles thrown in there. It's one name, but it consists of multiple different things, So name actually is not atomic. To put that in a database, correct, we would want it to be first name, last name, middle initial, maybe titles. We would want all of those to be separate attributes for the entity. When we think of another example, you may think of injury and an address. One address consists of a zip code, a street address, a state, a country planet kidding about the planet. Maybe for now. Anyways, that's not atomic, because an address consists of multiple things. If we wanted to that to be truly atomic, we would break that up into separate attributes. What is your street? What is your ah, house address, your street address and your zip code and your state, your country. That's things being atomic all over attributes within a database should be atomic. Thank you, guys. And I'll see you in the next video. 5. Primary and Foreign Keys: Hello, everybody. Welcome In this video, I'll be discussing primary and foreign keys thes air two very important concepts when it comes to databases, so be sure to pay attention. Let's first look at this example. You can see that we have three tables and they're connected by these bars, which represent relationships. You can also see that each table has its own i. D. These are each a column within that table. While these three are known as the primary keys, I will circle those. The purpose of these are to keep everything unique. Let's look at an example. If I scroll up here, you can see that this table has three. James Smith. We talked about this in a law previous video, and we realized that this causes problems because we don't know if these air the same James Smith's or to James Smith's or three. James Smith's real problem is is that there's no primary key. PK is short for primary key. Now we're all confused. So a solution of this would be to add an I. D column right here, maybe, and then, you know, give each one and I d. We could say this guy has the I. D of six, and this guy has an idea of seven, for example. And then this guy has an idea of I don't know what I did there of, ah 18. Well, if we apply that, we get something like this so you can see I added some I ds. Now James Smith here has 90 of 102 This one has one of three, and this one has 104 So this year is the primary key column, and these are the three rows you can see. It's a randomly generated number. This type of primary key is known as a surrogate primary key, now a surrogate key. There's there's basically four things about a surrogate key. Well, the first thing is, it's a number. It's randomly generated, which also means has no real world value. So it doesn't. It's not used in the real world. As a student, I d or whatever. It's basically just for the databases purposes. We can also see that they're all unique. We only have three rows here, but even if we had ah, 100,000 they should all be unique numbers. That means we have no repeating ID's. Finally, they should never be Knoll, and what that means is that there's no value. If we had this, we would have a problem, because now this. James Smith does not have an I d. So this cannot happen when it comes to primary keys. Now let's discuss foreign keys. Foreign keys are when we reference an i d from another table. Let's take a look at this review table. We have a user, I d. Right here. Well, this actually refers back to the primary key of the user table. These are important because they establish relationships. If this wasn't here, then this bar would not be here either. Which means reviews is its own separate thing. We have reviews over here, and then we have users over here and there's there's this big gap between him and there's no connection. Well, that's really not the case, because every single review should have a person who put three of you. I mean, you could think of like a websites that might say reviews and will say of the user. And then it will say something about the product, and it might say something like when it was posted and maybe a couple of things. Maybe a star rating. Oh, yeah, five stars so forth. Well, without this right here, the user, that's going to be bad, because now we just have a review that just has a value. And then if you really want to make your products so you could go in there and post 100,000 reviews and it wouldn't say who it's from, so it wouldn't really matter. That's why we need that foreign key. So that's basic introduction of primary and foreign keys. So thank you guys for auction and I'll see you in the next video. 6. What is a Relationship: Hello, everybody. Welcome. In this video, we will be talking about the basics of relationships. In previous videos, we talked a lot about primary keys, foreign keys and how we separate entities into separate tables. Well, just because we separate entities into separate tables doesn't mean that they're completely unrelated. There's often some connection between the two entities. You can think of a user and a comment on a website. The user is the one that post the comment, so the comment can't really exist without the user who posted it. Just because they're separate entities, a user and a comment doesn't necessarily mean they're completely unrelated. There is a connection there, and we designed that in a database using a relationship. A relationship is when we take a primary key and use it as a foreign key within a different table. If you need a little bit of review over primary key and foreign keys, check out my video over that. But anyways, the foreign key is going to use a primary key value as its value, and it's a reference when we designed the database, we structure the columns in a way that there is that connection so in the comments table. In this example, we would have a user I D, which would be a foreign key that references the primary key in the user table user i d. As for the actual values for the comments table, all of the user I D values must be a user i d from the user table. So each individual comment is going to have a relationship with one user will be getting into that in more detail in the upcoming videos. But for now, I just want you to know that there's multiple different kinds of relationships. There's three main classifications. Oneto, 11 too many and many to many. In the upcoming videos, we will be talking about thes conceptually meaning understanding. What kind of relationships are a 1 to 11 too many or a many to many, and then we're going to be going in depth of how to actually structure these in tables in a database. So first conceptual, and then we're going to go into physical, where we're actually showing you how to design those. So it take away for this video. Just understand that whenever we have a foreign key, this is going to be the star of a relationship. That's because if we had a database, we'd break up our entities into two separate tables, and we would have a you know, a user table and a review table or a comment table, for example. There's a connection here because the user I d. Over here references three user over here, the user idea over here. So this right here is the example of what I was talking about. This is known as a relationship, so thank you guys for watching and I'll catch you in the next video. 7. Conceptual vs Physical Relationships: Hey, everybody, welcome In this video, I will be talking a little bit more in depth. The difference between conceptually thinking about a relationship and physically designing that relationship. I personally think it's important. Teoh kind of think about things conceptually first, before we go in and actually try to create a relationship. Because if you go in trying to make a relationship or you try to design your database and you don't understand the concepts, how are you supposed to physically do it? You don't understand the types of relationships, so you don't understand the correct way to organize them. And I think that's a big fall in the way a lot of people try to teach this. They go in, dive right into the problems and try to design these relationships when the people they're teaching have no experience with what the relationships mean. So if you do already have some experience or you understand relationships a little bit, these next three or so videos aren't going to be super helpful. But you can still watch him if you would like to get information from them. But if you don't feel that the hope you go right ahead and skip over them. They're not going to be super important for the entire course unless you don't have the background of understanding relationships. Now, if you're not really sure, if you should watch him or not, definitely watch him, because I don't want you to dive into the upcoming sections of this course and be confused and lost and not sure what to dio so physically. Designing a relationship is when we take entities and attributes, we sort them into tables and columns. And then we decide which columns go, where which tables need to be created and how the columns connect, using foreign keys and primary keys. That is a step above just talking about Oh, this kind of relationship is when one person can leave one comment or whatever, it doesn't really matter. And because we actually haven't gone into the types of relationships, you might not have a clue what I'm talking about with the whole one comment thing. But in the next video, that's what we'll be doing. So I will see you there. Thank you, and I'll say next video 8. One-to-One Relationships (Conceptual): Hello, everybody. This video will be the conceptual part of 1 to 1 relationships wonder one. Relationships are when one entity has a relationship exclusively with one other entity, and that entity could be basically anything but because it's called a relationship, what better way to describe it, then with the relationship, you know, like with humans. So I took a week or two long our course and, you know, applied my skills. And I drew this beautiful picture for you guys, so hope you guys like it. So here you can see we have this beautiful girl. Her name's Penelope and this handsome man named, Ah, Pizza Pirate. And I can tell the future. But I'm just guessing if they had a kid, it would be named Penelope, which would just be really cool. But anyways, you can see that there happily in love and they have a heart floating around them. This is a 1 to 1 relationship, Pete The Pirate has one lady friend and Penelope. I mean, Penelope has one man friend their exclusive. If this guy went and got another girlfriend, Penelope would just be furious, and she would not be cool with that. And then they would no longer be together. Another way you can think about this is a man or a woman and a credit card. So we got this guy here and yeah, he's blue and he's a little weird looking and he's wearing a hat and you can say he's a college student, you know, and he wants to spend all his money. So he got this little credit card here and the specific credit card company Onley allows one individual owner for the credit card so he can't join own it. So what? What I mean by that is, if he had ah girlfriend and she wanted a credit card to she could not share this credit card and she couldn't do that. No, she is not allowed to do that. That's because this is a 1 to 1 relationship that means this guy owns this credit card on this credit card is owned by only this guy. It's exclusive now. If the credit card company wanted to allow people to own multiple cards or for multiple people to own an individual card, they could make it a one to many relationship. But right now this is a 1 to 1 so away. We could write this out in words is one guy, and then we drawn. Arrow owns one card, and he's Onley allowed to own one card. Also, this one card or one individual card among the group is owned by one guy. And of course you can draw this together. You could be like one guy, man. My handwriting is so bad getting a drawl, double arrows, and you can write, owns and is owned by down here. But it basically illustrates the same thing. Beautiful. That is how 1 to 1 relationships work. It's an exclusive relationship between two entities. Thank you guys for watching, and I'll see you in the next video, where we will be discussing one to many relationships. 9. One-to-Many Relationships (Conceptual): welcome everybody. This video will be explaining the conceptual part of one to many relationships. One to many relationships can be confusing. But if you think about it and you take time, sit down, process the information, it will make sense. So what? One to many relationship is when one entity can have a relationship with multiple entities . But one of these entities ever here can on Lee have a relationship with that one individual entity. Think of like a mother and a child. This mom can have multiple Children, but each child can only have one mother. Or you can look at my beautiful picture, which is probably going to make it so much funner. All right, so you can see here we have Penelope and Pete the pirate who also became king in the world . And you can see also picked up some other chick. And you can see that this lady is totally cool with it Now she is not bothered by it all. Well, she might be bothered by it, but you know she's still part of the relationship, so it must not bother her that much. This is a one to many relationship because one entity. This guy has many girlfriends. I just threw him as a king because if you think of a king of a country, they can kind of do whatever they want. And they might be like, Oh, I want 600 wives so they get 600 wives, but each of those wives, they're not allowed to go get multiple husbands. They have to be exclusive to that king. So it's kind of like a one king multiple wives, but each y I've wife on Lee has one husband, you see, or we can look at this boyfriend girlfriend example. This is a one to many relationship. One entity has multiple girlfriends, but each of these girlfriends Onley in a relationship with that one guy. You could also think of the credit card example I gave you earlier. This is eyes look creepy. There we go. I don't think that helped. This is a as how it's set up is a 1 to 1 relationship. But if we allow it to where multiple people can own the credit card, it will become a one to many relationship. So now we can draw a new person. Okay, it's a one of those. Ah, what are they called? Those horse people I forget. But she also owns this credit card. Now it's of one to many relationship because many people own one credit card. You can also flip that around because the way it's set up right now is a one to many relationship. Each one of these people can only own one credit card. That's a limitation set by the database, but we could also flip it to wear. One person can own multiple cards, but each card can only be owned by one person. So the way that would look is, ah, we would have a person who would own a card and then we would have ah, horse lady. He would own one card. Got a drawer arms. Ah, this is a one to many relationship. Still, that's because one individual card is owned by one person. But this one person, if they desire, can go get another card. So now each person owns two cards or they condone three cards, or they can own four cards. It doesn't really matter. The only rule is that one card can only be owned by one person. You can think of another example. Think of a comment on a website. So here we have a website and you're reading the comments and it's posted by this guy named , um, Cool Man five or Cool Nance. This is posted by one person. If this person wanted, he could go back and post another common. This is a one to many relationship because Conan's he can post Aziz many comments as he wants. But one comment Can Onley be posted by one person. So we have one person. Many comments. All right, thank you guys. In the next video, we'll be discussing many to many relationships. 10. Many-to-Many Relationships (Conceptual): Hello, everybody. This video will be talking about the conceptual part of many to many relationships. So in many to many relationships, you have many entities over here and many entities ever here. And they can all have relationships with whoever so one entity can have as many relationships as it once in any of those entities can have as many relationships as it wants. So let's think of this a little conceptually and look at my awesome picture, all right. Every single person in here except this person and this person is dating multiple people. So if you thought about, like separating this into boys and girls, maybe Gursel close enough. We have one guy here. Each circle is a guy and we have the girls over here. Each circle is a girl. This girl could be in a relationship with three boys, and then this. One boy could be in a relationship with three girls, and each one of these girls could be in a relationship with three boys. Sagan's E. It can get pretty complex, and because of that, it's actually not possible to design a many to many relationship in a database with good design techniques there are work arounds, which I'm going to teach you, and I'm going to teach you the best way to implement and many to many relationship within a database. But as for now, just know that a many to many relationship is this. So you can also think of the earlier example with the dude with the credit card. So he's pink and he owns a credit card. Yeah, and another person owns this credit card and surprise surprise. Another person owns this credit card. I'm so bad at drawing. Okay, there we go. And on top of that, this guy owns two credit cards, and this credit card is also owned by another person, which is down here. And this person owns another credit card, which is owned by also by this person and this person and this person and this person, and it just goes on and there's no really limitation. So that's many to many relationships. Conceptually, I'll see you in the next video. Thank you. And goodbye. 11. Sometimes You Decide on the Relationship: Hello, everybody. In this video, I will be talking about how sometimes you will be the one who gets to choose which relationship you want to use. When you're designing your database and programming your database, keep in mind as a database administrator, you will often be given business rules or commands that your boss or whoever you're working for tells you to do so That will dictate the kind of relationships you choose. But I'm making this video to explain that for a specific situation, deciding which relationship you're going to use is going to change what is allowed in the database and what is not. It's not the other way around to where it's a certain way, and you have to design your database to match that. Think of like the credit card example I've been using for the last couple of videos. If you want it to be where one person can own many cards but in a single card can't be owned by multiple people, that is something you're going to set up in the database. I often get questions. Actually, I get questions from like this all of the time where people asking me what kind of relationship is this example, and they'll give me an example. Okay, like a credit card and a person, that's that's what they'll give me. And then they'll ask me, Is this a 1 to 1 relationship? One of many or many to many relationship? And my answer is always It depends on what you want, the person and the credit card to do if you wanted to. Where one person can own many cards, but a single card can't be owned by many people. It's a one to many relationship. If you want it to where anybody can own any number of cards and an individual card could be owned by many people, you would make a many to many relationship. It depends on what you want your application to do. So that being said, it's always important to first understand what your data bases four. Before you go designing it in programming it because I'm not gonna be able to design the best relationships for the situation. If I don't understand how the database is going to be used, that's all I really need to say in this video. I just wanted to keep that in mind as you go into the next section that relationships are not something that are pre defined, and then you have to figure out what they are. Relationships are something that you define to enforce certain rules on your database. Thank you guys, and I'll see you in the next videos. 12. Referential Integrity: Welcome back, everybody. As you can see, I did get a new background back here, so hopefully that's good for you guys. This video, I just want to discuss a variety of topics because I wanted to kind of break apart the next part of this course because it could get a little complicated, a little confusing for those of you who aren't good at this kind of stuff. And I want this video to be for everybody. So this video is this gonna kind of explain some things that are important? No. Now, the very first thing that you might be wondering is, Well, what's really the point of all this relationship stuff? For example, what? We have two tables, right? And each table is a separate entity within a database, so this could be a website database. Now, I'm often going to use website databases, for example, because I just think they're really good illustrating and likely this will be a user table . And this will be some kind of comment table. Know what I mean by that? Well, you could have like a video viewing websites such as, um, this website you're watching this on, and you could have a video here with the play button and then a bunch of comments. Right? Or each one of these comments coming from the comment table is posted by a user. That is where the relationship kind of comes from. So this would be what's known as a foreign key connection So the foreign key would be over here, which you can write as F K for Short. And the primary key would be over here. Horrendous PK for short. Now what is it? Foreign key. Really? Well, we talked about a little bit once, but basically it's a reference now. What does that mean? It means it doesn't actually contain it's own data. It contains a reference to the original data. Now I can get a little confusing, but if you think of a user the sky right here, let's draw him out. And he's just chillin working out, lifting heavy things, you know? And well, we also have a comment by this guy, so he's like, Wow! And then he's like, Awesome! Now who are these comments by water by this guy? So let's say he has a user. I d. Of, um two off. This makes able. Well, we would say that this wow comment was by the user i d of 12. And this awesome comment would be by the user i d of 12th. This is a connection. These two reference this user I d. That means if for some reason this user I d changed to 13 which it probably shouldn't do, I'm just telling you illustrate this point that they're connected that then if that was the case, these would also change to 13. They are basically a reference to the original data. They are not their own data. So, for example, if we change this to 14 well, that means it would no longer be associated with this guy and would actually reference the user with the i. D of 14. So, basically, this user I d is very important and understanding who the comment is from now. You may be coming up with another question if you go to a website that you can view videos and we have comments down here. Well, it doesn't say Wow, this user was This comment was posted by the user with the user idea of 12. No, it says a user name now How does that work? Well, the way we would structure that is we would have the user table and we would have the comment table. And within this user table, we're going to have to user I D, which is associated with each individual user, and we're also going to have a user name. This is going to be an attributes of that user Now. Within this comment, let's give an illustration of a comment we could have. Hey, user ID equals 17 and then we wouldn't have the user name in here. So why wouldn't we have the user name in here? Well, that introduces a problem of basically repeated data, and we'll get to that in a second. So on a website how this would work as we would essentially combine these two tables in what's known as a join. And don't worry about all these technical terms too much if if it's overwhelming for you. But essentially we're going to be able to replace this user I d with this user name, but it's all done by reference. So rather than having the user name over here, let's say it's Caleb D. And also over here that can cause problems because what if Caleb D. Decides to changes, used her name and he changes it. Caleb See? Well, now we have conflict ing data. So basically, if that was the situation, we have a user with the user idea of 17 over here and a user with user idea of 17 over here with different names, and that doesn't make sense within a database. So that's bad design. That's why we have the idea of joints, which means we're going to get the user name from this table and the comment from this table and joined those into a new presentation of what the comment looks like. So on the website, you scroll down the comments that might look like this. Now this right here that's going to come from the comment table. But this down here that's going to come from the user table, right? And thats mainly just to prevent repeating data, because it imagine if we just stored everything in one giant table. If we distort everything in one giant table in this website and this is a small example, you can imagine how crazy can get. If we had tons and tons and tons of tables we would have, you know, user, I d. Call column, user name comment, and we would have something such as 15. Caleb be awesome. Then we would have 15. Caleb D. Great. Now this is what it would look like if we stored everything in one table and this can cause some serious problems. That's because Caleb D. Here is repeated twice now. Imagine if Caleb D. Commented on 200 videos. Now we have Caleb D. And here 200 times, and I can cause some serious problems. What kind of problems can that cause? Well, there's There's a couple, for example, Storage. It's going to take 200 times as much storage to store that one individual value of Caleb D . Because we have it in their 200 times and you can think of 200 people commenting on tons of videos. We're going to have a lot of repeating data, and that's going to be bad for storage. Also, we have it for basically integrity. Now what does that mean? Well, integrity is just keeping the database structured, how it's supposed to work. For example, on some websites you are allowed to change your user name. And if we did that and said Caleb, he change his name to Jake D. Well, now we have conflict ing data here and Caleb D. And Jake D don't match, but they're the same person because they're both referring to the user i d. Of 15. That's really bad. While we're at it, you can also see that the user i d is repeated. This is a big no. That's because this will be the primary key. A better way to structure this would be to have one user i d within the user table. So let's just say 1 15 and one. Caleb D. And now the two comments within the comment table we had, was it wow and awesome. I think these are both pointing back to the user with the user i d. Of 15 whose user name is Caleb D. So then, if for some reason Caleb he changed his name to J. D, it wouldn't affect these comments at all. That's why it's important to have entities separated in their own table. Because if you think about it, the person's name has has really nothing to do with the comment. Yeah, that's the person who posted it. But if you think of a comment, does a human's name have anything to do with a comment? No, it's a relation. It's related in a sense, that a user comments on it. But the user's name is an attributes of the user, not of the comment, because a comment doesn't have a user name, it has a user who posted it. That's why we reference it to a different table that's called a referential integrity, which I think I mentioned that earlier. So yeah, integrity. So those are the two main reasons I could go into 100 more if I really wanted to. But this video is getting along, and I think that's about it for now. So thank you guys for watching. And please be sure to check out the next lecture. Thanks, piece 13. Parent and Child Tables: welcome everybody. In this video, we will be talking about parent and child tables. So this is important Know about when you were talking about primary and foreign keys. Basically, every time we have at Primary Key or PK, it is a parent. Whenever we have a foreign key, it's a child. That is another way to think about it. Let me just kind of draw this out because it's going to make a lot more sense. If you have a table like, let's say, a user table. This user has one primary key of let's say, let's given a specific individual user. Normally, this table for the users would have multiple users, so would be spread out like this We would have. That's an awful table, but we have the user with the idea of 14 is Caleb and User I. D. With 15 is Caleb D. I can't think of any of their names, but for this situation, let's just think of one individual person rather than a bunch of people. So we have this guy on. Let's name him Boaz. Boaz has the user i D. 14. He is a parent now. If he's a parent, what does that mean? Well, if we're talking on individual specific entities such as Boaz, that means he has at least one row from another table referencing him. So you can think of a comment table. For example, within this comment table, we could have a comment. Hey, by the user with the user i d of 14. This row right here is a child. So what does it mean to be a child? Lift your child? That means you reference another row in another table. All of this is important to know when we're talking about relationships. That's because the parent is going to have certain rules such as you're only allowed to have one parent, four a child. Think of it like this is gonna make it a lot more clear. This one individual parent is referenced by 123 Children. Okay, so in this situation, this would be the primary key, such as Let's go with 14. And all of these are going to have a reference to that primary key 14 14 and 14. That's important to know. Understand that all of these are the Children, meaning they reference the parent of 14. Now there's another thing you can think about is a parent of a parent of a child. So here's a parent on Let's say this is the user table, OK, And here is a child. It doesn't even matter what it is, but this child can actually be referenced by 1/3 child. So we got a foreign key here and a foreign key here. So for this situation, he would be the parent here and a child here, and this would be a child. And that doesn't necessarily mean that this table was connected to this table in any way. The only way they're connected is that this is a child of this parent and this is a child of this parent. This video got pretty confusing pretty quickly, so don't get overwhelmed. Take it slow. If you need to go back and watch some of the videos or if you just need to do some extra research, you can always do that. You can check out my website to I have more tutorials on this. Don't get overwhelmed. All right. A lot of this is conceptual. That means you can kind of study in a fear radical way and concepts, but it's not really super practical until you actually implement it in a database design. I'm just trying to kind of scratch the surface of the conceptual stuff. I understand that conceptual stuff could get really super confusing. And sometimes people don't even agree on certain things. We can have one database expert over here say one thing and we can have another database expert over here say something completely different. So don't take this and kind of Don't take this conceptual cloud stuff like fluff and try to turn it into a concrete. This is how you do it. Okay? This is just concept jewel stuff. Just understand that tables can be Children, and they could be parents. The child is the foreign key, and the parent is the primary key. That's all you really need to know from this video. And from there you can go on and start learning how to design each individual relationship style. So thank you guys, and I'll see you in the next lecture piece. 14. NOT NULL: Hey, welcome back, everybody. This video I want to talk about NeuLevel columns also pronounced knowledgeable, depending on where you live. Apparently, I pronounce it wrong come from America's standpoint, but I don't really care. So basically, what I'm saying if you can't hear me, it's N u l l no, no, whatever. Basically, this refers to nothing, and I know that's gonna confusing. But no, is the absence of anything. Okay, so if you have a row with an attribute that's no, then there is no value within that. Entities attributes that kind of makes sense or not to make that way to technical terms. Think of it like this if you have a table for users and you have a user name column and for some reason you did not force people to have a user name and some guy didn't put a user name in well, then there's no value there. Therefore, when you do queries on it, or basically a query is, any time you search your data or do anything with your data, it's just going to return. No, now it's not saying the value itself is no meaning. The value off that specific spot in your databases? No, it means there is no value. Okay, that's kind of a conceptual thing. That's kind of hard to wrap your brain around if you are new to all of this. But get the idea that when it says no, it means literally nothing. Not an empty value, because it's the absence of a value. No, an empty value. Okay, it's different. So empty strings, for example. Just open quote, end quote. Those are not the same thing as no. Neither is zero. Okay, it's the absence of a value. Now you have the option when you create a database to set a column. To not know every single row within that column has to have a value. This is important when we start talking about designing relationships because we could have a foreign key column that is either labeled as not know or we could have it to where it's not labeled as not all. So this is going to change how our relationship works. For example, if we set our foreign key column to not know, that means every single row has to have a reference. If you think in the situation of a user and a comment database for a video website or whatever it is, it doesn't really matter. Well, it would make sense that the user I d in this table would be labeled, not know, for example, we would have a user i d. And that's going to reference the user that posted the common. This is going to be labeled not no. And that means every comment has a user who posted it. Now, if you think about it, that really does make sense, because without this right here, we would we were we would be able to have comments that don't have a user, which would just be weird and not good structure for our database. So this is a call, and we would want to label not know. Now let's think of an example where we don't want that here. We have a table for I don't know customers, maybe, and we also have a table for car rentals. This is a database for a car rental company and boom, We make this car table now. Within here we could have a column of the person who is currently borrowing that car or renting that car or whatever you wanna call it now? What if we have a car that does not currently have a person driving it? That means it would have a NOL within the user. I D column. Let's ask yourself, Does this make sense? Well, of course, it makes sense. That's because when you have a car, it doesn't necessarily have to have a customer who's borrowing it. For example, I could take the car back, and I would no longer be associated with that car except maybe in, like, the history of owners. But this is like the active current owner. Well, it doesn't really make sense. Toe have to have someone renting that car all times because then they wouldn't have any cars to give out because older cars would already have a user. And just in the practical world, it doesn't make sense. So this is not a situation when you would want to use not in all. So if you want to make it confusing, you would say not not know. That's basically all I wanted to cover for this video just in conclusion. Not in all use that when you need to force a column to have a reference That's when we're talking about relationships. Of course, you don't just have to use this for relationships. For example, a user name column you could use not know to require a user name within a database. Alright, guys. Thank you. And be sure to check out the next video piece. 15. How to Structure One-to-One Relationships: Welcome back, everybody. So the way this course we broken up from now on is that we'll split it up into two kind of categories. One is conceptual stuff, and then two is actual examples, so we'll have it to where we have for each relationship type one video explaining how to do it, and then one video of giving you examples. And then I'm going to do that for each relationship type. So let's get started. I guess. So we're going to discuss 1 to 1 relationships. This is another way to write that. Now. The way a 1 to 1 relationship works is you have one entity that has an exclusive relationship with another entity. That exclusive word is important. What that means is that no other entities could be in a relationship with that entity. Now the way this would work is with rows of tables. So within one table we would have rows, and each each row would be a specific entity that can have a relationship with a row of another table. And on Lee, one relationship can exist between two rows. So this row, for example, it's not allowed to have a relationship with this row and this row, that is, against the rules off 1 to 1 relationships. Now there's a couple things to note with 1 to 1 relationships. The really, really not that common, and you'll see why in a minute, the way it would kind of work to set up a 1 to 1 relationship is you would have to tables and you would have a foreign key in basically one of the tables that makes the association that it's related to the other. So let me just kind of draw that out here. We have two tables, and each of these tables are going to be their own separate entities, and within each table they're both going to have their own primary key. Every single time you have a table, it's going to have a primary key that is basically an I D for that entity. Now you're also going to store the attributes about that entity within that table. Those would be broken up. One column for one attributes. Now, what about the foreign key? How are we going to connect these entities? Well, you have to put a foreign key in one of the tables, which one do you put it in? Well, in reality, it doesn't really matter. That's because it's a 1 to 1 relationship. That means it's exclusive. So whether you put it in this table or this table doesn't matter. So, for example, we could put the foreign key in this table that's going to associate all of the stuff here with this entity right now. Let's see what it would look like if we did it the other way around. If instead, we put the foreign key on the other table. Well, that's going to associate all of this with this entity, right? So either way, it's kind of the same result. It's just flipped, so you can decide which one you want to put the foreign Keone. And if you really want, you could even put the foreign key in both, since they're connected. And if that's the case, you kind of just have this crisscross where this foreign key references that primary key and this foreign key references that primary key, which keeps them together. And that's not necessary and I'll show you why in just a second. But whichever table you put the foreign key and does not matter Usually, though, you will have what you kind of think of as the primary table. And then you would often put any of the what you would think as the Children table. You'd give them the foreign keys, but sometimes you're not gonna be able to think of which one's more of the primary table, so you can put it in either one. It just depends on your situation. Now there's another important thing to think about here. If we had this table over here that was basically connected to this table over here with a 1 to 1 relationship, we need to tell the database how to define that 1 to 1 relationship. And we're going to use something known as a unique column. And this is a basically a column characteristic that is going to describe the foreign key. What this unique column characteristic of this foreign key means is that every row has to have a unique reference. So if you think of a foreign key, let's say it's 72. Well, this 72 is going to reference a 72 in the other table, but we can't have two references to that 72 that would not be allowed. So that is how a 1 to 1 relationship works on Lee. One individual reference can reference a primary key, essentially so. Since it's unique, every row is going to have an individual unique primary key reference within the foreign key column. So if we have this person right and we have basically entities that reference it, well, we cannot have it to where two entities reference this person. It's not allowed because we have the unique column characteristic. That means this over here would have to reference a different person. Even with all of that, though, there's actually an easier way how you can store a 1 to 1 relationship. I'm going to explain that now. And since that entity is basically exclusive to the other entity, it's actually an attributes if you think about it, because if we have this entity and this other entity and there's a 1 to 1 relationship here , well, no one else in the entire database or nothing else in the entire database is going to use this. Accept whatever's in this table over here. That means we could actually take all of this information and store as a column within this table. Now there's limitations to that, and I'm going to explain those two. So if we have this table, rather than creating that other table, we could just have, you know, the primary key. No foreign key. And then we can store the attributes about the primary key. And then we can just store whatever that was in that other table as another attributes within this table, like, for example, a user name. I know I'm not supposed to give examples too much in this video, but this is just helping solidify, Ah, user name. Let's say Caleb D for a user account system that's exclusive to that person. So there's no need to make a whole another table for user names and then say, Well, OK, this user named Caleb D. References Reference sing this primary key. It's a waste of table, and with that table you have a foreign key and foreign keys actually slow your database down, so you only want to use them when you absolutely have to. So now that you understand that you basically have two possible solutions, one is create one table and use a column that basically stores all of the information that was in the other table. The Onley time, this really isn't going to work is if you have an attributes describing an attributes. This is actually something you do not want to do because then you would basically have this system where you have an entity and then you would have an attributes of the entity and then you would have an attribute that's describing the attributes. And I know that's kind of confusing now. But when you see the examples in the next video, it will make a lot more sense. The other solution is to have two tables, and you're going to have a primary key in each of them and also a primary key over here. I forgot to tell you guys that and then you're going to have a foreign key in one table, and then you're going to have the attributes about whatever the entities are. So these describe these individually Now, if you're kind of understood, just kind of confused about which one to choose. It really depends on if you have attributes describing an attribute. See, in this situation we have an attribute describing this primary key, so if we took all of this and stored it within an attribute of this table. We would have this problem down here of having this attribute describing an attributes and the reason why is because this attribute here describes this entity. And if we store that entity is that attributes, it would be attributes, attributes entity, See? Okay, So, yeah, it's a lot. It's kind of confusing, but it does make sense. But in reality you're going to do this solution almost all of the time. I don't know why I scribbled it out, but this one check, it's good you want to do this. Okay? And to be honest, this really doesn't really define it as a 1 to 1 relationship because of 1 to 1 relationship is across multiple tables when you use a foreign key. So this actually gets rid of the 1 to 1 relationship altogether. So once we Seymour examples in the next video, this will really solidify our skills. So please check that out. And if you have questions, be sure, let me know. I'll try to answer him. Thanks. See in the next video 16. One to One Examples: Welcome back, everybody. In this video, I will be giving you examples of what we talked about in the last video. So we discussed 1 to 1 relationships and we gave two different ways. We could basically store these in our database, and I'm just gonna go over those briefly. The 1st 1 is when we had two tables and we put foreign key in these and also define it as unique. It doesn't really matter which table you put the foreign key in as long as that association is unique. Otherwise it wouldn't be a 1 to 1 relationship. The other solution was toe have one individual table and store this entity here as an attributes within here. And that really brings up one question. How do you know if something is an attribute? And to be honest, it can be tricky sometimes, But just you just have to think about the data. I'll give you an example how it could be confusing. Sometimes, for example, we could have a user table, and within here we're gonna we could have like a user i d and a couple other attributes about the person. So we have the first name last name and email, and you can see here we have email as an attributes. Well, what if we wanted to store as an entity? Of course we could do that. We could essentially take this email and instead store in an email table. Let's say his email is cool. Dude, wanna one? Ah YOLO dot com and we could store this in here now. Then we could put a foreign key that references three user I. D. You see, it can be kind of confusing on what you want to dio. So this could either be an attribute or can be an entity. It's really up to you. You see now the only time it would really have to be an entity for sure. And it couldn't be. An attribute is once again, is if you have attributes describing it. For example, if you have, you know, e mail and then you had maybe an email provider. Well, the email provider describes the email, not the user. So storing in the user table wouldn't make sense. If we store this email provider under this email, we would have what's known as a transitive dependency, and that sounds like a really big word, so don't don't get too caught up in the language. But basically that's saying kind of like a double dependency. And what that means is basically, the email provider depends on what email we have in the email that we have depends on what user we have. So that's bad. We never want to have a transitive dependency within our database. So then we would have to store the email as its own entity and you can see with database. There's a whole bunch of little details you gotta piecing together. So this is gonna take some studying. I'm giving you all the material you need. You just might need to take the time to put it on flash cards or practice or watch these videos 80 times if you want. It's just important to understand all of the details. Basically, if this is the case, you need to do this this indication. You do that right, so just understand. It's an art. It takes a lot of practice. You can just pick up a pen and start drawing beautiful pictures on day one. No. First you have to learn how to hold a pencil or pen then you have to learn how toe right and so forth. Then eventually you can start drawing Portrait's. It's works the same way for databases telling you I mean, look, my drawing. Is this so bad? All right, it takes practice. I don't practice drawing. I practiced database design. So think of it like that. You practice, you'll get good. So let's think of another example of a 1 to 1 relationship if we had a user table. And within this user table we have the primary key, of course, which would be user I d. And then we have a user name. Well, if we decided to rather in store this as an attributes, we decided to store as an entity distort as an entity would need to set it in its own table . So this would essentially be a user name table so we would no longer stored in here. We would store in here, and then we would have a foreign key to whoever's user name it. Waas. This is not good design because you see it's unnecessary. You don't need to store this user name and a whole another table. You're discreet eating this new connection is foreign key of primary key connection, and that's going to slow down the database. You do not want to do this. There's no reason you would have to store it in here. The only time you will ever need to do something like this is if we have a one to many relationship, which we'll talk about in the next video. So in this situation, since we don't have any attributes about the user name, we don't need to store as an entity. That means we can take this user name, put it back in the user table and then just basically toss out this whole other table. We don't need that wasted space. So like we started, our table would look like bone bone bone would have the primary key and then a user name, and that's the best way to store. That's because user name there's not going to be two people with the same user name. If this was the case, you can see that it's a one too many relationship, and that defines a completely different set of rules. So if if it's a true 1 to 1 relationship, you will never have something like this happen where two entities can have the same other entity that breaks the rules. So you guys that sums up examples for 1 to 1 relationships. Just remember to think about it. You either store in two separate tables if you have attributes about the entity or you store in one table and distort as an attribute simple. Is that so? Thank you guys, and I'll see you in the next video. 17. How to Structure One-to-Many Relationships: Hey, guys, welcome back This video. I will be discussing one to many relationships. This video. I would just be discussing how you design them. And in the next video, I'll be giving you examples and doing that design with those examples. So just like the 1 to 1 relationships except now on the one to many relationships now there's something I would do want you to understand when we're talking about one. To many relationships, when we're talking about one of many relationships, it's usually like a parent child kind of style. So the parent is going to have the primary key than all of the Children are going to have the foreign key. So it's really easy. It is up to design one too many is by far probably the easiest one to wrap your head around . It's probably the most common to so one to many relationships, and many to many relationships are the most common 1 to 1. Relationships aren't that common, because when you a store as an attribute within the original table, it's not really considered a 1 to 1 relationship anymore. It's rather just an entity in an attributes, you understand. So when We're talking about one too many. It's actually over multiple tables. So let's kind of just give you some data. Here we have a table, which is the parent which owns put PK, which means primary key. And it's of the Children have a foreign key. All right, now, the way I'm drawn it now, it looks like I'm drawing like, multiple tables, But I'm really not okay. The way it works is you're gonna have to tables. And these are just a specific entity. So, like, a person and a comment or like a car And, ah, manufacturer, whatever the way it's gonna work in the real world is you're gonna have a table and you're gonna have basically a primary key column and then mawr attributes. Then you're going to have another table. This is the child table, essentially, and you're going to have the primary key, which is an individual counter for this entity, and then you're going to have the foreign key and then you're going to have attributes. Now, each child is going to be a new row within this same table. So you're not creating multiple tables. You're only creating one table for the child, so each column is going to be an attributes about as many Children as possible. I mean, that was kind of really confusing way I worded that. So basically another way to think about is each row could be its own individual child. All right, so let's give some example Data. We could have a PK of seven PK of eight and a foreign key of seven and then PK of nine in a foreign key of seven and then 10 and then ate. This eight is going to reference a different row or a different parent so we could have gate over here. So the way we have it set up here we have essentially two parents one to and a total of three Children. This seven from the parents table is the parent of number eight and nine, and I don't mean that sequentially. It's not like 789 These numbers are completely unrelated. And then eight is the parent of 10. And the way you figure that out is from this foreign key. We got seven over here and seven over here, and then we have eight over here and eight over here. So when you're working with from scratch. You might already have everything. Comme Bob elated in one giant table. And as we've learned, that's a really bad way to structure things. And you might be wondering, Well, how do I transition from one table to two tables? So if it was like that, you would have the primary key, and then you would have basically the Children in here as some sort of attributes, which is bad. So we would have the child also within the parent. Now, you might have attributes about that child to, and then finally, you're gonna have attributes about the parents. And like I said, this is a transitive dependency. We talked about this in the last video works the same way with one to many relationships. While you're going to dio, you're gonna take that problem, call him right here and all of the attributes, and you're going to kick him out and put him in their own new table, which will be designed just for that entity. So rather than trying to force an entity into an attribute right here, we're gonna take that child, and that's gonna be the new table. And it's going to have its own primary key. And now we're going to Onley have the foreign key in this table. We're not gonna have it in this table. Why is that? Because let's kind of just draw some entities, okay? We're just gonna represent entities, is circles, right? And we have three Children bomb, bomb, bomb, and these are all gonna have the foreign key to the parent. Well, the way this is set up now is we have the three Children and then we have one parent. This is the best way to have a sit up. We don't want to have a foreign key within this here. I'm gonna do in blue to represent that it's different than the rest. You don't want to do that. Hopes that was wrong. You don't want to have the foreign key up here that references the Children. Why is that? Well, that's because we were We could only have one column for that foreign key, and you can try to set it up some weird, funky way. But I'm telling you right now, if you want to design it the right way, the PK has no foreign key. So the table that the primary keys in the the parents table has no foreign key. That's an F of my handwriting is so bad sometimes. All right. So let me just kind of illustrate that if we tried to store a foreign key in the parent, this is the parent. And let's say this is the child table. Let's say that the PK is five, and then we have a foreign key column, which is, let's say, six. And then we have more attributes about the primary key or the entity of the table. Well, if we wanted to storm or foreign keys, they're basically say we have multiple rows within the child table. Well, then we would have to create a new row within the parent. So it looked like this. So the primary key five. Let's just say it's some entity over here is a child off. So I have to be an eight. I was a child of three different entities, and this is just a really bad way to illustrate it. That's because now we have this repeating data right here, and it's unnecessary because we configure this information out just from the trial because we could essentially figure out within this child table right here. We have multiple rows, and this road could have a foreign key referencing the parent and a primary key right here . And it could be, you know, 67 we got another one for eight. And we can figure out that these three are the child of the person with the primary key or the entity with primary key off 14 for example, we could we could figure that out from the child table. We don't need to store the foreign key within the parent. Now, I know my drawings have been kind of crappy, and this probably is this more confusing than it has to be, but the correct way to design it it's apparent. PK attributes child table. We're gonna the p k f k attributes This foreign key will reference this primary key. And just this is another side detail. But I'm a throat in here. Remember that we still have that Not Noel option. If you wanted to. Where every single child has toe have an a parent, then you would label it. Not in all. Thank you guys. If you want more examples, be sure to check out the next video. Thanks. And I'll see you later 18. One-to-Many Examples: Welcome back, guys. This video be giving you examples of one to many relationships. Now I'm gonna give you the example I already gave you with the users and the comments. I gave you this a couple videos ago, and I think it's just awesome for illustrating my point on a user comments system. There's usually another entity involved, for example, a user comments on a video. But for this sake, I'm going to ignore that other entity. It works essentially the same way. It's just on the other side, so you'd have, like one entity connected to one entity connected at one entity. So this would be like the user commenting on a video, for example. But I don't really think it's necessary to talk about that for this video. So the way we have it is we would have a user. One user can make multiple comments. Now these comments can be on essentially whatever video are product or whatever the system is. It doesn't really matter. That's what we're going to ignore for this because we're just thinking these comments are going to be on whatever they could. Even we could even have to comments on one individual video, but that doesn't matter for the sake. So he made three comments here. This is a one to many relationship. That's because one user posted three comments, and on Lee, one user can be the poster of a comment. So this individual comment right here can't be posted by two people. This would not work if we had another person. You also posted this That wouldn't work. Is his face upside down with Ah ah, Okay, whatever. That's great. Be so it's a one to many relationship. Now let's talk about how you would structure this in a database. Well, you would have. Ah, first you would have the user, which is one entity, and you have the comment, which is another entity. And I'm going to write down the columns that are going to be in these tables. So the user obvious is going have a primary key, and so is the comment. Every single individual table is going to have a primary key. Keep that in mind. It could be a combination of multiple columns if necessary, which we'll talk about that soon. But every table will have a primary key, and the comment is going to have a foreign key that references three user. So this foreign key references the primary key. I could do that in blue. If that helps visualize it. Boom. So much more clear. Now each individual row within a comment table can have a foreign key that's associated to a specific user. So if we took this comment table and blew it up and actually put data inside of it, it would look something like this. Well, we got the P k f K and then attributes so the PKK could be doesn't really matter. But every single comment is gonna have a different one. The F K is gonna be the user, so we could have repeating data. One user could post more than one comment and the attributes that could just be what the common says now for the situation. We have one user here, number eight, who posted two separate comments. Then we have a second user who posted one comment. That's kind of how it would look now. What about the user table? How would that look? Well, the user table would be simple. It wouldn't have anything to do about comments inside of it. We would just have the primary key, then maybe some attributes. So, like, you know, use your name, a first name, last name and email and maybe a sign up time or date. But there's nothing to do with the comment in the parent table. So that's something to remember. When you have a typical parent to child entity relationship like this, where this is one table and these air all within one table as individual rose, then the parent is not going to have any information about the Children. We talked about that in the last video. That's because it's going to create redundant data. We can already figure out that this row is a child of this table, and this bro is a child of this table, and this row is a child of this table. We do know I need to do it the other way around and say that this table is apparent of this row in this table was apparent of this road, and in this table was apparent off this row. It's redundant and unnecessary. Let's think of the other side of the situation with the actual video where the user comments on. So now The comment is posted on a video one video and have multiple comments. But one comment can only be on one video. Okay, so this is a one to many relationship still. So let's see how this is set up. One video has multiple comments. That is how this would be set up. So within the video table, we would have, you know, the PK, which would be video I D or something like that. Then we wouldn't have a foreign key because this would be the parent in this situation. And then we would have attributes about this parent. And then the child, on the other hand, is going to have PK, which would be a comment I d. And we would also, you know, have like a user. I'd even here so we would have a foreign key to the user, and then we would have a foreign key to the actual video. And then we would have attributes about this individual entity Now see the same thing in this situation. The parent is on the right and the child is on the left. So in the parent, we do not have any attributes about child. We do not. That is not what we do on Lee. The child has attributes about the parent. So that is how you would design this within a database. Look at this right here. This would be, you know, video I D. And then we could have, you know, video title, video description, tags, all that stuff for the attributes and then for the child, we have the primary key. The comment i d Foreign key to the user foreign key to the video and then attributes about the comment, which and I basically covers everything. The only other attributes I can think of is like, you know, when it was posted, for example, or if it was a reply to another comment. So you guys, that sums up one to many relationships? Be sure. See the next video. We'll talk about many to many relationships. Thanks. 19. How to Structure Many-to-Many Relationships: Hey, guys, welcome back This video I will be discussing many to many relationships and how you designed those. Now the city will be how to do it in the next video. Give you examples. So this one is basically only to apply to any situation you have now, if you remember a long time ago in a long video ago, I said that it's hard designed, many to many relationships in a database the way they conceptually work. They work a little differently when you actually put them in a database, and that is what I'm going to be explaining in detail in this video. So basically, I start off with the two entities that I'm working with, and I just put him in boxes just kind of illustrate that they would be their own separate tables. So we have the entity one an entity to now, the way the many to many relationship would work is that one of these entities can have a relationship with many of these entities. I'm just gonna put was gonna use circles to represent like entities like individual specific entities. The reason this is so complicated it's because one of these entities can have a relationship with many of these entities, and it just get really, ah, messy and complicated pretty darn quickly the way you structure in a database, rather than having what's known as a M to end, which is many to many now, don't think of these as short for many because it's actually just a variable. I mean, it's just saying that any number off entities can be in a relationship with any number of entities, but they don't want to use the same number because that number can vary. So if if you did basically like em helps If you did m m well, then it be assumed like seven seven. So once you have your two entities, what you need to do is break it up into 21 to many relationships, and for this you're going to need what's known as an intermediary table. This is just one name for this table. You confined him with all kinds of different names, but the concept stays the same for it. Whatever you want to call this table. So the way it's going to work is you're going to have your first entity over here and then an intermediary table which combines the two and then your second entity over here. And rather than these entities connecting just like together like this, you're going to basically connect each one to this intermediary table. As for the relationships, it's going to be a one to many, this direction and then a one to many this direction. This is the parent. This is the child. This is the parent, and this is the child We no longer have. This parents of parents who are also Children, kind of weird, creepy thing. So when it So when we're talking about primary key and foreign geese, the way it's going to look is you're going to have your main table over here and your main table over here. Both of these will obviously both have primary key of whatever the entity is, whatever the entity is, that's going to be the primary key. So, like use your idea. Your car idea customer, I d. It doesn't really matter, and then you're going to have the attributes about this entity. Then in this intermediary table, you're going to have a foreign key to both of the primary keys from both tables and these air going to reference one of the primary keys in another table. So, like I said and one to many relationships, the parent does not contain a foreign key to the child. It's just the child that contains a foreign key to the parent. Now, what about the primary key? What's the primary key for this table? Well, it's actually the combination of these two foreign keys, So basically it could be six and six six and 76 and eight, or could be 86 eight and seven and so forth. Basically, you can repeat a specific one, but you can't have the same repeating value, so I could not have 66 twice. That's going to be a conflict. So, basically, for this to work as a primary key, the combination of the two have to be unique. Even though I use six more than once, I use eight more than once. That's okay. As long as I don't repeat 66 or 67 or six, then you can also store attributes here about the combination of these two things. Now what do you call this table? That's a good question. Well, usually you'll take this entity and you will take this entity and you'll combine them to make entity underscore Entity, you know, like a book and then an author. And then that intermediary table would be book underscore. Author. So how does this work when we just want to know the specifics of the data? Well, we would take the primary key of this table over here and put it in this intermediary table , and then we would use the primary key from over here and put it in its intermediary table, which is basically saying there's a connection between the idea over here of seven and the idea over here of eight. Now, what if a has a relationship also with number six? Well, that, like this, is going to be confusing. But if we put it in like this, we could say eight six. So now we're not actually creating a many to many relationship. We're just making 21 to many relationships, and that allows us to resolve that issue. And we can use this as many times as we want, rather than basically just pointing it all back to the same value. We could make two references to the same primary key. And then basically our table could look like in the end, it could be like 777777 for the primary key or the foreign key to the first table. And then it could be like 717273747576 for the foreign key to the second table. Now this is kind of too conceptual. It's hard to understand with us practical data, check out the next video where I will be giving you some real world examples. Thanks, guys. We'll see in the next video peace. 20. Many-to-Many Examples: Hey, what's up, guys? Welcome back. This video we will be talking about many to many relationships and we will be giving some RIA World examples of what they will look like. So let's start off with a social networking website. So we have this sweet website and you can sign in. You got all kinds of friends and it's cool and all, except how in the world do you structure your database? So we have one friend over here and this guy's like friends with two people, right? I'm just kind of illustrating. Imagine these as like, the profile pictures know exactly the best drawer, so it doesn't quite look like that, but that's what it's Mr represent, so we can get kind of confusing pretty quickly because this guy's friends with this guy and this guy. But these guys are friends with these guys this, but turns out thes two are actually friends with this guy and this guy's friends with this guy and this guy's friends with that guy and that guy's friends with some other guy over here. And then it turns out there's a girl on the social network and she's friends with that guy and that guy and that guy and that guy and that guy and that guy and that guy and that guy . And then there's this girl that we're here, who's friends with that girl and Becca, you get my point. Basically, it gets complicated so that we need to break this up into to talk about number here, one too many relationships. So before we would just have, you know, like user table. But how do you represent, like, friends on here, right? It's confusing because what do you what do you do? Connect him to another user table? It's kind of weird. So basically, in this case, the user table would kind of connect to itself, saying like, Oh, this row of this user table was friends with this road, the user table. But this is not optimal. You do not want to do it like this because then we have, like, all these roads that are connected with each other, and this rose connected to 10 rows. And we can't do that because we'd have to create more columns and just be a big mess. So what we need to dio is we need to have this intermediary table. So to illustrate this, let's say this one. Guys over here and this other guys over here, that is a weird body. Uh, it's on a ley ahead. Kill him. Anyways, this guy over here has the i. D. Of 42 and this guy over here is the I. D. Of 78 we want to say that their friends will Then in this intermediary table, which would be like friends, for example, we would say 78 is friends with 42 now 78 is also friends with this guy down here, which is 108. So it's a 78 100 eight. Turns out this guy's also friends with this guy, so we would have 108 and 42. So now we're no actually having any duplicate data. We are having the same I d. In here, tons of times, for example, with this guy over here had 1000 friends. Will then be like 1081081081081081 await. Wanna wait and just keep going and going and going. But that's the least amount of data we can use. And the good thing is, it's all references. Since these Air Foreign Keys they only reference back to one individual person. We just have to store them in another table. So we're not actually having duplicate data. Were just having a lot of references. And that is probably the most efficient way to store. All this data is within, like some kind of friends table. It's going to take that entity and put it in the friends table. For every single friend he has, that means, whether is 74 or 104 or 104. I'm sorry, 100 yet 104 or 74. The direction doesn't matter. You want to have both of these in here to say 70 fours friends with 104 and 100 for ISMs. Friends of 74 you would say just one of these, and the database is smart enough to understand that that means there's a connection between two people. One guy has the idea of 74 another guy has an I. D. Of 104. We connect those and the friends table. Yeah, well, that makes sense. I'll give you another example, since probably little more easier, understand? Could have books and authors, which I talked a little bit about in the previous video. Now, if we're talking about books and authors, we might have a book table and we have an author table. Well, how do we illustrate the author of each book? Are we supposed to add a call him like author one author to author three, author four. If we did that, Well, then, for all the books that only have one author, all those other column is gonna be no, which is just wasted space. Now what if we make 10 columns that say Author one author to author three. Author four, Offer five. Author six, Author seven. All three. Author nine, Author 10. Well, what if someone comes in raw along and his book is written by 11 people? Then we're going to have to restructure our entire table to add another column. And that doesn't make sense because we basically want to design the database to last. We don't have to go back and edit the structure every time because that's gonna take forever and just slow things down and also just waste space. Because then everybody for the 11th column for the 11th author is gonna have a null value except one. And that's just a total waste of space. Okay, so how would this work? Well, we'd have the intermediary table, and it would be book authors or book author. Now book. Let's say we have a book with the idea of 12th and it was created by three people. John, John and Joan. Okay, these Air Three people And they all have their unique I. D. So let's say 12 48 and 72. So the book, which is 12 and a user with 12 don't get that confused. Oh, they're different things. So they're going to both be put into the book author so it would look like this. 12 12 12 48 12 72. These reference the people and all of these reference one book and then were able to take all that this information and use a join to give us back a basically a new view of what our data would look like, where it's going to say, you know, book three Little Pigs, by John John and John you get that? You? That is how you would structure a many to many relationship. And in the parents table, you're just gonna have author I d book I D. And then the combination of author and book I d Foreign keys will make the primary key within the book. Author table. This is some nice drawing right here. Right. So you guys, Thanks for watching. I hope you enjoyed these story ALS and I will see you next time.