[Maximilian} So let's continue
with the restaurants table.
We do already have a restaurants table,
but it doesn't have the structure we want.
It has the name field, which we need,
but then it has a type field,
which I don't wanna have any more
instead I wanna link to a separate types table.
And therefore, actually,
before I continue working on restaurants,
I'll create another new table.
And that will be the table that holds
all the different restaurants types we'll have.
And therefore, I will name this table types.
You could also name it, restaurant_types
if you want to, but I'll just go for types.
All the defaults here are fine.
And now we can add our columns.
As in, basically, all my tables,
I wanna have a unique id, which is an integer,
which is my primary key and not null
and which should be auto incrementing.
And I'll then add a second column,
which is the name of the type.
So where I stored the actual type description,
something like Italian.
You could also name this description,
therefore, I'll go for name.
And here, let's say, we allow
words up to 100 characters long.
And I also wanna have NOT NULL values here
so that this may never be omitted.
And that's already all.
It's a quite simple table,
only has an id and then the name itself.
And I only created for the single reason
that I then have a table
where we can have predefined types
so that you can't just enter any arbitrary values
when you add a new restaurant,
but that you instead have to link
to one of these predefined types,
which could be managed by some global administrator
or anything like that.
We could have done the same for countries.
As I mentioned to the last lecture,
there I didn't do it so that we can finish
creating the addresses table like that.
But for types here, I wanna show you this approach
of having such a predefined list of values.
Therefore, here I'll click Apply.
That's the SQL statement that will be executed.
And we apply that to executed.
Now, we got the types table.
Now, we can work on the restaurants table.
And now I wanna change the structure
of the restaurants table.
And that, of course, is something we haven't done
up to this point.
Now, to change it, we can right-click on restaurants here
and actually choose Alter Table.
Alternatively, we can drop the table and recreate it.
Now, if I would choose Alter Table,
we get almost the same wizard,
which we get for creating a table,
but we have some columns already.
But here we could now also delete one of the columns.
For example, delete the type column
and then add any new columns that we want.
Of course, if we do that on a table
that already has some data stored,
that means that if we add new columns,
the existing data won't have any values in those columns.
So altering a table can always be a bit tricky
if it's a non-empty table,
because you need to deal with that existing data in there.
And therefore here, since we did only create a table
to play around with it anyways,
I'll instead rather drop it and recreate it.
So I'll choose Drop Table here.
We can then review the SQL statement that will be executed.
So that's the code you could execute on your own
to drop a table.
And if you executed, that table gets deleted.
This can't be undone, so you should be careful about that.
Now, that it was deleted, I'll create a new table.
And of course, again name it restaurants,
keep all the defaults up there.
And add my good old id field again,
which is auto incrementing, a primary key, and NOT NULL.
And then in there, I wanna have the name of the restaurant.
And that again, should be a VARCHAR of 255 as a length.
It should also be not null.
But now things will get interesting.
Now, I also wanna link to a specific address
and a specific type.
And for this, we add a brand new column
and the convention for the column name.
Here, typically is to reference the table
to which you wanna link.
For example, address.
However, not the plural form,
which is the actual table name,
but the singular form,
because we will link to a single record
in that related table.
So in this case, just address,
and then underscore id,
or whatever the unique key column name
is you chose in that other table.
In my case, in the addresses table,
I have an id as a unique value for every record.
And therefore here, I will link to address_id.
Now, that's just a naming convention.
Choosing this name on its own, doesn't do anything.
It's just a convention to signal
that the value that will be stored for this column
in the restaurants table
will actually be a key value
that can be found in that other related table.
In this case, the addresses table.
Therefore, since I plan on storing
one of the id's that I have an addresses,
in this address_id column in restaurants,
of course, the data type should be the same data type
as I have it for my id in addresses.
And there, I have an integer,
so I'll choose integer here as well.
Now, we can make this NOT NULL if we wanna enforce
that every restaurant must have a related address.
And here I wanna enforce that, but other than that
that's basically it.
Now, you might note that here at the bottom,
you have this Foreign Keys tab in this wizard.
You have a bunch of tabs, which we haven't touched yet.
And most of these tabs are a bit more advanced
and we don't need them,
but I wanna have a look at the Foreign Keys tab here.
Here, indeed,
you can set up more details
about such a foreign key relationship.
Of for example,
how related records should behave
if one of the related records gets deleted?
It's a bit more advanced and not something you have to do,
and therefore, we will ignore it here.
We dive deeper into that in a dedicated SQL course
or in the official SQL documentation.
Of course, in case you wanna learn more about that.
For the moment, we'll ignore it and go back to our columns.
And therefore, in the end, that's all we have to do.
There isn't anything special about it.
It's a regular column just with a special name
that follows a certain convention,
but only for following the conventions sake,
not because that name would do anything special on its own.
And we wanna ensure that we picked the right data type
so that we later can store id's from our addresses table
in that restaurants table, in that column here.
And therefore, that will enable us to later link
to a unique id from the addresses table.
And I wanna do the same for my types.
So therefore, I'll add a type_id.
And I'll choose integer here and make this NOT NULL,
so that I later can also link to types in my types table.
With that, if I click Apply,
you see it's still a regular SQL statement
for my restaurants,
but now we have these two special columns here,
which don't look special, but which we know
will be used for storing id's of records in other tables.
And therefore, I'll apply this to recreate
my new restaurants table with these special columns added.
Now, as a last step, I wanna add the reviews table.
And for this I'll create a number new table.
And that table will be named reviews.
Now, as all my tables,
it will have an id field and that will be a primary key,
NOT NULL, auto incrementing and an integer.
And then we saw the kind of data we wanna store
for a review on that slide.
For example, we wanna store the name of the reviewer,
the person who left the review,
we could also work with a separate table for that,
so we could store our reviewers,
the persons who leave a review in a separate table
and link to them.
But I wanna keep things a bit simpler here,
so I'll just store a reviewer_name here.
And that can be a VARCHAR because I wanna store a string,
I just wanna reserve more bytes here
and allow for longer strings to be stored.
This should also not be null.
Then we need the actual rating and that should be a number.
It should be a number, an integer,
because we wanna leave a rating
of one, two, three, four, or five.
So I'll choose INT here.
INT here make this not null,
because I always wanna have a rating.
And of course, I don't wanna allow any integer in there,
but only one, two, three, four, or five.
But that is something we can enforce
in the application that would talk to the database.
So in our NodeJS code, for example,
there we could validate any user input
and make sure that we only save a one, two, three, or five
in our database.
So here on the database side, this approach should suffice.
You can also add some constraints and validation here
when defining your tables schema, your structure,
but that's all it's a bit more advanced
so we'll not dive into that here.
Instead, we now have the rating
then next column I wanna add now
is the text of the review.
And this could now be a longer text.
So whilst we could go for VARCHAR
and allow maybe 1000 characters,
which would probably do the trick,
we can also choose to special text type here.
Simply to also show this to you.
A TEXT is a type you can use
for storing longer text in your column.
And here, you also don't need to specify
how long the text should be,
instead you can just use texts like this.
So TEXT is a special type,
which you would use for long pieces of texts.
Maybe not necessarily required here for the review text,
because it won't be that long.
It might be more fitting for something like a blog post
or a product description.
But nonetheless, to also show you this type,
here we'll go for TEXT.
We could choose not null here,
but actually we could also say that for the review text,
that field might optional.
You can leave a rating without entering a text,
and therefore, I will not make this not null.
Now, the next column I wanna add is the date.
The date of a review.
And for this, we also have special types we can choose.
We got the DATE, and the DATETIME, and the TIME,
and TIMESTAMP, and the YEAR value types.
Now, they are all a bit different.
For example, DATETIME contains a time part,
whereas date is really just a date.
So a single day in a year,
which does not know anything about the time.
Now, here I'll choose DATETIME, so that for our review,
I do store both the date and also the time
when the review was left.
And here we can delete those parentheses.
I wanna make it not null, so I did check this to ensure
that we always have a DATETIME.
And now I'll add a default expression here,
where I will actually write CURRENT_TIMESTAMP,
written like this.
This will utilize a built-in feature provided by MySQL,
which will ensure that this DATETIME
is actually set automatically whenever we add a new entry,
a new record into the reviews table.
So that we don't have to find out
which date we currently have and set it manually
when we enter a new record,
but that instead it will be set automatically.
A very convenient feature,
which we can unlock by setting CURRENT_TIMESTAMP
as a default value for this date column.
And with that, we're now almost done.
The last column I wanna add is now my restaurant_id,
because every review should be linked to a restaurant.
And hence, I add the restaurant_id column
here to the reviews table
so that for every review that's stored here,
we know to which restaurant it belongs.
I don't store a review_id in the restaurants table
for every restaurant,
because a single restaurant can have more than one review.
Whereas one review will only have one restaurant
to which it belongs.
That's why I add my restaurant_id here in the reviews table.
And I'll set it to the same data type as my id
is in the restaurants table, which is integer.
And I'll set it to not null
because every review must belong to some restaurant.
And now with that all we can click Apply.
That is the SQL statement that will be executed.
And that will give us a reviews table,
which meets the requirements outlined on the slide,
and which will ensure that we can store all the review data,
and that we link a single review to a restaurant.
So let's click Apply, finished this.
And now we got the four tables
that I also show you on the slide before.
Now, that we did all those tables,
it's now finally time to add some data.
And then of course also to see how we can query that data,
especially also to see how we can query data
that's related across multiple tables.