Okay.
Welcome back.
So we're finally here.
This is a bit of a turning point in the course.
Really important section not to scare you too much, but really fundamental to how SQL and my SQL are
used in the real world.
So this is a section all about relationships and also something called joints, which we'll get to in
the second part of this section.
So let's focus on the relationships part first.
So far in this course, we've been working with very simple data, things like books or users where
we only have a couple of columns per table and that's been by design.
We need that simplicity in order to focus on what's important, to isolate the different features that
we're talking about, whether it's functions or logical operators, it's much easier to teach those,
and we don't have 20 different tables to worry about, and we can just basically drill down into one
table and focus on that.
But that's typically not how the real world works, where, yeah, we might have a user's table, but
there's no site that really can just get by on having users unless it's featureless.
There is plenty of other things that need to go along with users, whether it's comments or likes or
tweets or posts or photos or any sort of, I don't know, order transaction.
There are all these different things that we could have alongside users.
So we've been working with simple data, but that's about to change.
Real world data, unlike what we've been working with, is really messy, typically and interrelated.
There's a lot of connections between data.
So some of those things I talked about with, let's say, users, if we're working with just a simple
blog site, so nothing like a Facebook or a complex social network, just a simple blog, we still need
to have user data.
We need to have information about posts, blog posts.
We also need to somehow keep track of comments and then tags.
And that can get pretty complicated already.
Not to mention things like advertising and tracking not only ads on their own, but how ads are related
to users and what users are clicking on and not clicking on.
There's so much data that just from a simple blog site, something that may not seem that complex.
There's so much stuff that we could store.
So we're going to focus on how we work with interconnected data in the next couple of sections.
So we're no longer focusing on one table.
We're going to see how do we work with two tables or three or four that are related.
So not just tables that exist independently, but tables that reference one another and are interconnected.
So let's get going.
And the first thing that I want to do is go back to our books data.
So remember our books table.
Don't worry, we're done with it.
We're not going to be typing any books code, but on its own it was really simple.
We had a books table with author, first name and last name, a book title released here, a page count
in a stock inventory, I believe a stock quantity, and I think that was it.
So again, on its own, that just represents a single book, but you can't really do much with that.
But if we are running a sort of book site where we sold books, at a minimum, we would need to keep
track of things like versions.
So we didn't even talk about this.
But there's often multiple versions of a single book.
If you go on Amazon and you look at a book, let's say Harry Potter, the second book, Chamber of Secrets,
well, there's going to be the initial British release, the UK version as a hardcover, and then there's
a paperback, and then there's the American version, and then there's a paperback.
There is a full color illustrated version that came out relatively, relatively recently.
It looks pretty cool that has these giant illustrations.
It's the same title.
How do you keep track of that?
Is that a different table or do you just make a separate book for every one of those?
But if you do that, then how do you make sure that they're connected?
Because when I go on Amazon and I look at the hardcover, it also will show me below.
Some people also buy paperback version, or it's also available in, I don't know, 25, 40 different
languages.
So there's all sorts of things to worry about just with versions.
But then we have authors.
Well, that might seem straightforward.
We have an author, first name and last name, two columns on our books table, but that's not sufficient
when we have multiple authors.
So some books, a lot of books have multiple authors.
What about if it's a research paper that we're selling or a journal that has dozens of authors potentially?
So there's a lot of things to keep track of there that we can't really do right now.
We only have room for one author in our columns.
Then a whole big thing is customers.
If we're doing a book shop online, books on their own is great, but we also need ways to keep track
of users or customers is what I'm calling them in this case.
So customers on their own also can't do that much, but we might keep track of their email log in information.
Of course, we wouldn't just store their password directly, but for simplicity's sake, let's say we're
keeping track of password and email so that they can log in.
And then there's a whole bunch of other things.
Orders.
So if customers want to order books, how do we store that?
We need to create a new order and it needs to be associated with a customer who created it.
It needs to have address information.
Maybe it needs to have the contents of the order.
Are there books?
One book?
How do we support that?
How do we handle that?
What about if we're working with dates and times and we need to keep track of when a return period expires?
So we need to have a an order date and then keep track of 30 days or 60 days after that or whatever
it is.
Then we also need to store or somehow keep track of the transaction information addresses for billing
address, not only shipping address, the type of shipping, the billing information.
There's so much to store.
And then a whole other thing is reviews.
So if we go on Amazon or pretty much any book site, any book that you look at will have dozens or hundreds,
sometimes thousands of reviews.
So we've got reviews, their ratings, that's the number of stars.
But then also a title for the review and then a caption and then the actual content of the review,
which may be ten characters.
I hate this book, however many characters that is.
Or it could be a giant paragraph, someone's love letter that they're writing to a book.
So how do we account for all of that?
And then the last thing that we'll talk about are genres.
So you may think that we could just get away with storing this in a book, which we absolutely could.
We could just have one genre.
But many books don't cleanly fit into one genre.
We might have a vampire novel that's also historical fiction because it's set in Victorian period.
England Or we might have a sci fi book that has elements of fantasy or who knows, you can have all
sorts of cross-pollinated genres, so only assigning one genre might not be that useful.
We may want to have a bunch more.
And in fact, on Amazon you can see there are books that will have two or three different genres or
different tags associated with them.
So what I'm trying to show you here is that we were working with books on their own, but in a typical
app or a typical website that has to do with books and a bookstore, in our case, there's a lot more
that goes along with books to make anything functional, to store any important information.
And this is, I don't know, maybe half of the tables that you would need to get away with kind of the
the MVP or the bare minimum.
There's still many other things.
Something like orders, for instance, might actually need to be split into a couple of tables.
We might need to have an address section or an address table.
We might need to then have a separate billing table.
We might need to have shipping.
There could be all different tables that we could split it up into.
So this isn't to say this is the end all, be all this is just that.
There's a couple of other tables that we need to consider at the bare minimum.
Okay.
So we'll stop here in the next video.
We're going to identify some of the different types of relationships between data at a high level,
what are the different types of associations?
And I'm back.
I totally forgot to show you this.
Even though I wrote a note to myself, I totally skipped this.
So I'm back after recording this, adding this to the end.
I want to show the schema diagram that I really think highlights how messy data can be.
So here's I think I showed this early on in the course.
This is just a potential schema for Facebook and it's definitely incomplete.
It's maybe a 10th of the number of tables that Facebook has.
But you can see just to store basic things like events and users, we have an event, Facebook event,
we have event membership which is related to RSVP states, which is also related to venues, which is
also related to groups and to workplaces and to Facebook profiles.
And it just goes on and on and on.
So what I'm trying to show you is that data can get messy.
And in fact, here's another super intimidating schema.
This one is a little bit crazy because it has to do with storing protein database.
I think it was I saw some article on IBM and I believe MIT, and fortunately it's very low quality image.
So you can actually see the tables.
But each one of these little rectangles is a separate table and every line is a connection between the
tables.
Now, this is the worst I've ever seen, the craziest.
And I think that was the point, is that they're trying to just show you they're never going to actually
look at this to try and understand anything.
But they're trying to show you how complex protein information is when you have.
All these different combinations and permutations for a given protein.
Without going into too much detail here, this isn't a bio, of course, but the way that a protein
is folded, the same structure, basically the same amino acids, depending on how it's folded, can
drastically change the features, if you will.
Basically, what the protein does, how it behaves.
And so this is a way of documenting using SQL in some way the structure of a protein using a database.
So not to scare you there.
I just think it's kind of a powerful image.
Although grainy, it is powerful to see basically some really messy, ugly gross data that can be supported
using tables.
It's just doesn't look pretty.
But that's the important part.
And kind of the point of this video is I wanted to get you prepared mentally for working with some more
complex data, although nothing that looks like this.