So we're going to build a blog.
And therefor I of course want to create a database
that has a posts table that holds all the blog posts.
Now, of course, there are dozens of ways
of storing post data, but for me,
a post that should be stored in the database
should have some unique ID
by which we can identify this post.
This will also be important
for fetching and displaying a single blog post,
and for updating and deleting blog posts.
We will need that unique ID for that.
And then every blog post should have a title,
which, well, is at the top of the blog post, I guess.
Then a short summary excerpt which we show on
the All blog posts page, where we can see all the blog posts
and we see the short description.
And then I wanna have a body, so the full blog post text,
which is not visible on the overview page,
but on the detail page,
once we clicked on a single blog post.
Then I wanna have the main body that will be displayed.
Now, a blog post should also have a date.
So that's simply a timestamp
where the blog post was created.
And then I also want to have information about
the author of a blog post.
For this we could, of course,
just store the author name in my post here,
but instead we're going to do it as we learned it
in the previous course section,
and we're going to normalize our data,
and we're going to create a separate authors table,
because here every author should also have a unique ID.
Basically every table that you create
has a unique ID for its records.
But then I wanna have a name and an email address
for the author.
And that's why I wanna have a separate table for that,
for one, so that we don't have to re-enter the name
for every new blog post we create,
because chances are high that we only have a few authors
that write all our blog posts,
and it would be very cumbersome and error prone
if we have to re-enter their names for every new blog post.
It would be easy to add typos
or write the name slightly different,
even though it's the same author.
And in addition, since the author is not just
defined by its name, but also by the email,
we don't wanna squeeze all that data into the posts table.
Instead, having a separate authors table
which we connect to the posts table
is the much better approach,
and how we typically should structure our data
when working with SQL.
Well, and then of course, to establish this connection,
every post should have an authorID,
which links to the ID of an author.
So to the ID of a single record in that author's table.
Now, of course, the different columns which we will have
also should have different data types.
And for the ID, for example,
it makes sense to use the integer data type,
both for posts and the author, and the author of course,
for authorID, we also have integer,
because that always has to be the same data type
as for the primary key in the table we're linking to.
And then of course, we have more columns here,
and for the title and the summary,
it makes sense that we use VARCHAR,
so a string with a maximum length.
And then for the body, we can use the TEXT data type
since that could theoretically be a very long text
and we don't wanna impose any limits there.
For the date we can use the DATETIME data type of course,
to store both the date and information about
the exact time when this was inserted.
And I wanna store it such that
it's automatically set to the time
when that post is inserted, so that we don't have to
manually determine that's time.
And forward the authors, their name and email again
can be strings because this shouldn't be too long of a text.
You won't have a maximum length there.
And hence these are probably value types that make
a lot of sense for the kind of values we're storing here.
Now, of course, this is just one way of
structuring and storing this data.
As I mentioned before, there would be many other ways of
defining a post and authors,
but this is fairly simple yet still we have a connection.
We will be able to join data and therefor
this should be great for practicing what we learned
and for applying what we learned
in our NodeJS driven website.