Now that we did dive into those
basic Crat operations,
creating, reading,
updating and deleting,
that we need all the time when working with data
and databases.
It's time to move on and work towards that
goal that we defined earlier in this course section.
To dive into SQL based databases
and to practice what we learned now.
We wanna build a database table
or preferably multiple tables
that allow us to store Restaurants data
and Reviews for those restaurants.
Now of course in the previous course lectures,
we already did add a Restaurants table
that holds some restaurants data
but that's not all the data I actually wanna store.
And I also wanna store the data a little bit differently
and we didn't touch the reviews part at all.
So that's also something we'll have to add.
And now the thing here with the layout
you see on this slide,
is that this structure of storing the data
is not really optimal
because in some fields here,
like the Address field,
we have complex data.
We have data that actually consists
of multiple pieces of data.
And data for,
I actually don't just want to work with two tables here,
but with more tables
because when working with SQL
and that is of course,
something we'll dive way deeper into
in the complete SQL course that we also have.
When working with SQL you typically
store your data in a normalized form,
which basically means you don't wanna have any complex,
nested or structured data in one table
but instead you wanna split your data
across multiple tables.
And for example, here in this restaurants reviews example,
we could work with multiple tables
where we actually store our data like this.
We could have a Restaurants table in the middle here
where we have a unique ID and the name
but then we don't store the address
and also not the type here
in that Restaurants table.
But instead we have separate addresses
and types tables,
so that the address data
is split up across multiple columns
in a separate table.
And we just kind of connect a single restaurant
to a specific address
by linking to the unique ID of an address.
And of course you will learn how that linking works
and how you can query related data like that
later in this section here.
And we could also outsource the restaurant type
into a separate table as simply so
that we can't enter any type as
a type for a restaurant.
But that we have a table with predefined restaurant types,
like German, Italian Indian,
and we just link to one of these types
when we create a new restaurant.
And similarly for the reviews,
we could have a standalone table
and we would link a single review
to a specific restaurant.
Again by specifying the ID of that restaurant.
And there would be different ways
of storing this exact data.
You could possibly even split it across more tables
but this is a table design,
a database design
that would be quite common for this exact use case.
And that's why I'm showing it here
because this is a key thing to note
when working with SQL databases.
You work with multiple tables.
You have a lot of involved tables
where every table stores
quite simple data,
and then you'll link those tables together,
typically by relating to the ID of items
in another table.
Like I'm showing it here on this slide.
Now of course, we're going to implement this layout
and this structure over the next lectures.
And you will also learn how you
can then query data across multiple tables.
So how we could, for example,
say that we want to get a specific restaurant
and the address data that belongs to it
but to get started,
here's a little challenge for you.
I want you to add such a new addresses table
and I want you to create it with the commands
or approaches you saw over the previous lectures.
And I want you to add these columns,
which you see on this slide.
unique ID's,
streets,
street number,
to this addresses table with appropriate data types.
Think about the kind of data
that will be stored in the columns
and then pick the appropriate data types
from the types you learned about over the last lectures.
And then once you're done in the next lecture,
we'll build this Addresses table together,
so that you can compare your solution to mine
and we're then also going to adjust
and add all the other tables
and make sure that we can populate
all those tables with data.