So now that we have the database abstraction class, it's time that we define the schema, which is
the tables and the table columns.
In this project we are going to have three tables the users, the posts and comments.
And they are going to interact with each other.
Previously we had only one database table.
So by creating multiple tables we are going to learn some new concepts like the foreign keys, the concept
of an owning side, and the concept of a one to many relationship.
And now I'd like us to jump to the code editor and define those tables using SQL.
And along the way, while we type, I am going to talk about all those concepts.
So by the side I'm going to have a preview of SQL syntax that lets us create tables.
We did that before in the previous project.
The difference now is that we're going to introduce a new thing which is a foreign key.
and we are going to define multiple tables.
So let me add a schema SQL file inside the database folder.
We did it the same way previously.
This is where we keep the database structure.
Now I'm going to enable Capslock as the convention is to write SQL using uppercase.
Now lowercase is also fine.
That's just a convention.
So to create a table you can write create table.
If not exists because of the not exists part, you can safely run the same query a couple of times,
and it will only try to create the table if it doesn't exist yet.
Now let me disable Capslock.
That's the users table.
Now you can see that I'm just using this syntax that's visually represented here, nothing else.
So I'm going to define three tables.
First that's users.
That's posts and that's comments.
Then we're going to add columns for every single one of them.
And finally, I'm going to talk about the foreign keys and relationships.
So first the users first column is ID, then again enabling capslock.
And this is an integer that's a primary key.
So unique identifier for every record.
And it is an autoincrement.
Which means we'd like this to auto increment every single time.
We won't be setting this column manually.
It's always set by the database.
We'd like such column in every single table.
That's why I'm just going to copy and paste that.
So every single table needs a primary key.
Next up some unique columns for the users.
Every user should have a name.
This would be text, not null.
Then every user needs an email.
This is also text but it's unique.
So there can't be two users with the same email as email would be used as the login, then the password.
This is also text, not null.
Now the password will be hashed, but this is done on the PHP level, not on the database level.
Another thing we're going to have is role.
So we're going to have something called role based access control.
This means you can have a role of a user admin etc. and your permissions will be decided based on your
role.
This is not new and needs a default.
Default is just user.
That's someone who can comment and then created ad.
That's a date time where the default is the current timestamp.
It means we will never manually change this column.
It will be always populated by the database using the current time.
So some of the columns can be reused across different tables like Createdat.
It's really useful to have timestamps at your tables so that every single time a row is inserted, we
just know automatically when this happened.
It's very useful for debugging to figure out if and why something went wrong.
So next up I'm going to add the basic missing columns to posts and comments.
And then we're going to be talking about the foreign keys and relationships.
So this here the posts needs a title.
Every post needs to have a title and it can't be null.
It also needs a content.
So basically the content of the blog post, which also can't be null because it doesn't make sense.
Otherwise we're going to be counting views of every single post.
This would be an integer defaulting to zero, so it's also never null and then the comments what do
we have here?
Well, I think that there should be just Content, which is text and it's not null.
And then we can jump to adding a foreign key.
So the foreign keys they are references to a primary key of another table.
And when strictly enforced, the foreign key can't point to a non-existing primary key in another table.
So what does it mean in plain English?
Examples always work best, so consider we have a post.
The post should have a user ID column.
That's why we can know who wrote that post.
The user ID is a column on the posts table and it will be a foreign key.
This means it will point to a user and to identify a user, it will point to a primary key from users.
Essentially, this means that in this posts table we need to add a column that will be called user ID
and it needs to be an integer.
Why?
Because it needs to be of the same type as the primary key.
It points to and the id inside users, which is a primary key.
As you can see, is of type integer.
So it points to the user id.
It can't be null.
So that's the first step before we define the foreign key.
That's just a column.
It's not a foreign key yet.
To add a foreign key you add that at the end of the table definition.
By writing foreign key you point to the column or columns that are the foreign key.
In our case, that's the user ID and it references the users table and specifically the ID column in
that table.
This is how you define a foreign key.
So we definitely would like to have the same user ID inside comments, because comments are also written
by a specific user.
But what's more, every comment needs to belong to a post.
That's why we add another foreign key, which is the post ID, and we need to define it separately.
So another foreign key is for the post ID column, which references the posts table and inside it the
ID column.
This is generically called a one to many relationship.
For example, that means that one record from the users table can be associated with many posts was
one to many, so one to many.
Relationship between user and posts, and also one to many relationship between post and comments.
Additionally, you might hear a concept of an owning side.
That's just an abstract concept, and it's often used to describe a table which has the foreign key.
That might sound counterintuitive because you might think that user has many posts, but when you describe
the owning side, you would say that post is an owning site because post contains a foreign key to the
user's table.
Okay, so we've got this concepts explained and visualized, and we've also got the SQL written.
So just make sure you didn't make any mistakes.
You need all those commas.
It's super important.
You need the semicolons at the end of every single declaration.
Check out the code under this video.
If you didn't make any mistakes, that's super crucial.
And in the next video, we are going to run this schema.
We're going to create a CLI script to load this into our SQLite database.