OK we'll come back.
So we're finally here and this is a bit of a turning point in the course really important section.
That's scary too much but really fundamental to how sequel and mines 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 when 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 we might have a users 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 long side users.
So we've been working with simple data but is 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's really simple.
We had a Books table with author first name and last name a book title released year 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 books 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.
Looks pretty cool that has these giant illustrations.
It's the same title.
How do you keep track of that is at 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 you know some people
also buy paperback version or it's also available and 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 on line books on their own it's great.
But we also need ways to keep track of users or customers.
This 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 e-mail log in information
of course who wouldn't just or their password directly but for simplicity's sake let's say for keeping
track of password and e-mail 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 the customer who created it needs to
have address information maybe and needs to have the content 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 you know 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 that billing information so much store and then
a whole other thing is revues.
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 you know 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 10 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.
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.
You might have a vampire novel that's also historical fiction because it's set in Victoria period England
or we might have you know a sci fi book that has elements of fantasy or who knows you can have all sorts
of you know 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 typical Web site that has to do with books and 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 the candidate
the MVP or the bare minimum.
There are still many other things something like orders for instance might actually need to be split
into a couple of tables.
You might need to have an address section or an address table which I 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.
OK.
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 this schema diagram that I really think highlights how messy data
can be.
So here is 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 tenth of the number of tables that Facebook has but you can see just to store basic things
like events and users.
We have 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.
Unfortunately it's very low quality image that you can actually see the tables.
But each one of these little rectangles has 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 you know 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 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 that protein does how it behaves.
And so this is a way of documenting using sequel in some way the structure of a protein using a database.
OK.
So that's a scary 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 just doesn't look pretty.
But that's the important part in 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.