In this tutorial I’ll walk you through how to create database schemas for Employers and Applicants and define the relationships between Users, Employers, Applicants and Sessions using Drizzle ORM in a Next.js + MySQL job-portal application.
⌛TIMELINE⏳ 0:00 Introduction to database schema for Employer and Applicant 0:32 What user selects at signup – Employer or Applicant 1:30 Asking 6 questions (based on relations) 2:40 Answering the previous asked 6 questions 4:00 Schema file path 4:25 Understanding the Employers schema 9:25 Applicant schema for the same 11:40 Syntax of tables & relationships in Drizzle ORM 12:10 Users relations with Employer table 15:20 Users relations with Applicant table 16:00 Important tips to keep in mind 16:20 Session relation with User table 17:15 Tips to get best at coding 17:40 Comment if you have doubts 17:52 Support my channel
Hello, what's up guys? Welcome to the next session of MySQL Present Job Portal Application.
Today is our day number 31.
And today we are going to see how to create a database schema for our applicant and employee tables or profiles.
Okay.
And along with that, today we will see how to create or how to define the relationships between the user versus your applicant and your employers.
Okay. I guess the word versus is not right.
But yes, we have to define the relationships between them.
Because we are using a SQL and SQL is all about your tables and then relationships.
So here you can see very clearly that whenever a user registers for the first time,
then what does he select?
He gets an option in the role.
And that is to select either a job applicant, basically either an applicant or an employer.
He will select one of these two.
And once the user registers successfully, then again he does not need to register again.
Next time he will log in.
By the way, there is no need to log in if he comes within 30 days.
Because we have implemented auto-login.
We have added all the sessions.
So what I mean to say is that if we look at the relationship here,
then whenever someone registers,
he will be added to our table and that's the user's table.
But the same user based on this role,
either applicant or employer,
will be added to this table.
So let's see if we say that if there is one user,
then how many times can he create an applicant profile?
Next question.
If there is one user, if he is registering,
then how many times can he create an employer profile?
Third question.
If there is one user, if he is registering,
So these questions should come to your mind.
And if these questions come,
then the fourth question will be,
What is the relationship of one user?
What is the relationship with the applicant?
What is the relationship of the same user with the employer?
So you will have to think about such questions,
you will have to understand.
Only then will your application be made.
Only then will the full stack project be made.
And guys, I would like to tell you one more thing.
If you haven't checked out the playlist yet,
then please do it.
Because here...
It must be somewhere.
Here you can see this part very clearly.
User stable schema.
So if you haven't checked out this video yet,
then check it out too.
Because you will get an idea of what this schema is.
And we have already created a schema of user stable.
We have also created a schema of sessions.
So today we have to create an employer and what?
We have to create an applicant.
Okay.
And here I have already kept all the designs.
One user can only create one applicant profile.
This was our first question.
When one user registers, then it is fine.
But he will either select the employer or the applicant.
So if one of the two becomes one,
then it becomes clear to us that
one user can create only one applicant profile.
And the same one user can only create one employer account.
Can create a profile.
So these 2-3 questions have cleared our doubts.
And the same user can neither be an applicant nor an employer.
I mean, at the same time,
the employer's profile and the applicant's profile
both cannot hold.
And the fourth question was,
what is the relation between the user and the applicant?
It's one to one.
And what is the relation between the employer and the user?
It's again one to one relationship.
So okay.
We got to know this much.
Now what do we have to do?
We just have to create it.
And it's super easy.
But before that, I would like to tell you guys
that it takes a lot of hard work to create such videos.
So please, if you like my hard work,
if you are getting some value added from my videos in your life,
then please support me.
And if you want to support me,
then guys, I don't want anything.
Just subscribe to the channel and like the video.
I will be very happy.
And it will help me a lot.
Okay.
So I'll go here.
So here, guys, you can see our schema.ts file.
Basically, where does it come from?
It comes in Drizzle.
Where does it come from?
You can see very clearly.
We have over here.
Okay.
The schema is here.
I have come here.
Here I have to create the employer and then the applicant.
By the way, I have already created it.
So you can see, guys, I have copied and pasted it here
so that it becomes easy for you to explain.
Because you will understand typing.
I will also understand.
Okay.
And by the way, we have already seen it.
So it's not like it's new.
But yes, it is necessary to understand things.
Some things are definitely new here.
For example, whenever you create a schema,
you have to assign it to a constant variable.
Right?
Right now, I am creating for employers.
That's why I named it employers.
And we are also exporting it.
Because if I have to insert any data on the employers' table tomorrow,
then through this, like employers.name,
if I have to update the name,
then I can target it like this.
Okay.
So this is the use case.
And then second, if you want to create a schema
and we are using a Drizzle ORM,
then the syntax there is something like this.
In which language are you using Drizzle ORM?
So it's like MySQL.
If it was Postgres, then it would be different.
So this is the table of MySQL that I have to create.
So basically it is like this.
MySQL table is a function that you have to write.
And here you can see the name of the table.
This is the MySQL table that you are going to create.
And this is the name of your table
which you will see on your database.
By the way, you will see it here.
This is the table name.
Whatever you will write there, you will see it.
Okay.
So this is done.
And then these are the options.
Basically, you are passing the object.
In which you have your fields.
Like if I tell you, I already have it.
So see.
It will be very clear.
ID, name, description, banner, image, URL.
Yes, pay attention to one thing.
These two fields that you see,
what we have?
We have avatar underscore URL.
Banner underscore image underscore URL.
Okay.
And all of them are in short.
Pascal convention is here.
I guess it's a Pascal.
I will put here which one it is.
But whatever it is.
I hope you got to know this much.
This is the table.
And schema means that you are creating a table.
And at the same time, you are defining its structure.
Which fields should be there.
And what is its data type.
This is the beauty.
Okay.
So which fields will be the name of this table?
First of all, we need an ID.
Obviously.
Okay.
So in employees, we have one ID.
Which will be the primary key.
So here you can see very clearly that we are referencing it.
From whom?
From the table of our users.
From the ID field inside it.
And by the way, we are writing on delete cascade here.
Its simple meaning is this.
If the user's records are deleted from the table of users yesterday.
So if the same user's records exist in the table of employees.
So it should be deleted automatically from there.
Or else if you delete the users.
Right?
But if the user's records still exist in the table of employees.
Then it is wrong.
Because the main user is not there.
I am giving its reference.
So if I have the value of users.id yesterday.
So I will match it with the employees.id and find out.
From which user does it belong?
We gave the reference.
Okay.
Then we need a name in the table of employees.
Which is a variable character.
Length is this much.
Description is Avatar URL.
I deliberately showed you here.
This Avatar URL and this banner.
I told you to look at it carefully.
Because when we actually work.
So we are doing camel case like this.
We are using camel case.
Avatar URL.
But here text will be there.
And then Avatar URL which you can see.
This is what you are writing after the text.
So this is going to be stored in your actual database with this name.
You are going to see it on the database with this name.
When we are coding.
So we are directly.
Employers.AvatarURL.
I will access this URL and add the link.
And regarding what is this text?
What is the difference between varchar and text?
Though both are comes under the string.
But what are the differences?
When should I use varchar?
When should I use text?
So for that.
I highly recommend.
I made this video in great detail.
If you see this one.
Varchar vs text.
Please check it out.
It is very important.
It is simple.
Then what we need?
We need a banner.
By the way this avatar URL and banner image URL.
When we make a dashboard.
Then it will be useful.
What is the type of organization?
What is the size of the team of that company?
Year of establishment.
When that company was made?
I got it.
And yes.
We are not representing HR.
We are representing the company.
Website URL.
Their location.
Delete it.
Add.
Create it.
Update it.
All are timestamps.
In which automatically.
When you register today.
You have made an employer.
If you have to update some profile tomorrow.
It will be automatically updated.
By which I will know.
When was the user created for the first time?
But.
This updated one.
Timestamp of updated ad.
Keep updating.
It will be automatically updated.
So.
These were the things.
Employers.
I told you.
It's very easy.
Just create a table.
It's just.
Here you are giving a schema name.
And then by the way.
This part.
Applicant.
Same.
If you have to represent the table of the applicant.
Sometimes insert.
Sometimes operation.
So you will target the applicants with this name.
Again.
MySQL table.
This is the first argument.
Name of the table.
Here we have the name of this table.
Applicant.
Okay.
And here also same.
You can see.
Date of birth.
Nationality.
Whatever.
And again.
Same id.
Same user id.
Why did we do cascade?
Because.
I have that user's id.
So I can find out very easily.
That user's id.
Exists in our applicant table.
That is.
With the help of this id.
Its biography.
Date of birth.
Again.
Now I have to import this.
So yeah.
Oh my god.
I guess I did it with JWT.
While I had to do MySQL core from here.
Okay.
So I go here.
Yes.
I didn't have to do it with JWT.
Okay.
Done.
And then what we have.
We have nationality.
Then marital status.
If you see.
We have made it NM.
What happens in enumeration.
You give an option.
Either he will be single.
Or he will be married.
Or he will be divorced.
Nothing else.
We can give him.
In this field.
Okay.
Like drop down.
Like even.
This was also NME.
Okay.
Team size 50.
Okay.
What is written guys?
Year is not defined.
How can this be?
So guys.
We had done year.
MySQL year type is telling.
One second.
Let's go to top.
Year.
Year is here.
So what happened?
Okay.
It came.
So see.
Here this NM.
This was also NM.
Job and then employer.
Okay.
See here.
This was also your NM.
Admin.
Applicant.
Or employer.
Okay.
In the same way.
It's just simple.
Gender.
Then education.
Again it's an NM.
You have to select a value.
Like option.
You have to select an option.
Experience.
Applicant.
So how many years experience.
Its website URL.
Its location.
Delete it.
Create it.
And then update it.
So this was very simple.
I hope you got to know.
Now one more thing is left.
And that is.
Very important.
Very funny.
So this is something like this.
Where.
Now we need to define the relationships.
Between each table.
Right.
So if you see.
I have done syntax here.
In this way.
Export const.
One second.
I show it like this.
Export const.
This table.
Name.
Relations.
Whatever name you give.
Relations.
This is a function.
Which you have to write.
In that.
You pass two arguments.
First is the name of the table.
And the second one.
Is your callback function.
In that callback function.
You can see.
You are destructuring.
Helper functions.
That's one.
Or many.
Okay.
And in that.
There are further things.
I tell you.
Like.
First of all.
I want to get the relationship of users.
I am assuming.
That my user.
Because of that.
Everything is happening.
User is the main.
Parent.
You know.
Table.
So now.
What is the relationship of that user.
With an employer.
What is the relationship.
With an applicant.
What is the relationship.
With a session.
We have to tell that.
And.
This is the syntax.
Whenever.
First of all.
You understand the syntax.
Then your whole life will be easy.
It is simple.
It is written here.
Export const.
And then table name relation.
Whatever you have to give.
You give.
Then relation is a keyword.
I mean.
Is a function.
So.
User relation is given.
I have written relations here.
No doubt.
You have to import it.
From drizzle.
We have imported it.
So now.
This is the name of the table.
Name of the table means.
This is the topic.
Users.
Now.
What is the relationship.
Of this user.
With this session.
What is the relationship.
With this employer.
What is the relationship.
With this applicant.
This is the topic.
So.
This is the first argument.
Table.
Whose relation.
You are going to tell.
Yes.
Users are my main.
So.
What is its relation.
With other tables.
I have to define it.
Users.
Then we have this callback function.
In that callback function.
We have.
Two helper functions.
Destructured.
Which is your relation function.
It gives us.
That's your one.
And then many.
These two.
Have a helper function.
Okay.
Then we have this callback function.
In that.
We have passed the object.
One by one.
In which.
First of all.
My user.
What is its relation.
With our employer table.
Relationship.
One user.
Can have only one.
Employer profile.
So.
This user.
Means.
One to one relationship.
See.
One.
What did I tell you.
This one.
And then many.
These two.
Are your helper.
Function.
So.
That's why.
After one.
You can see.
It's a function.
First argument.
Again.
Name of your table.
My user.
Can create only one.
Employer profile.
One user can have one.
Employer profile.
After that.
Guys.
We have an object.
You can see here.
Both one and many helper functions.
Take the arguments.
To define the relationship details.
Now.
Between users and employers.
The relationship details.
It is not defining.
We have fields.
And then we have this references.
So.
Here fields.
Means.
Your source table.
Target.
Like.
Who is your source table.
This is your user.
So.
If tomorrow.
You have to find any records.
Of employer's table.
Then where will you search.
In source table.
Obviously.
Users.
Id.
This is your references.
Means.
Target table.
Who is your target.
Employers.
So.
In employer's table.
If you have to find any records of user.
Then.
Employers.
Id.
So.
In this way.
Between them.
This is the relationship.
Just remember this.
Fields.
Means.
Your source table.
In that.
If you have to find any data of employer.
Then.
Where you can find.
Through which field.
Obviously.
Id.
And.
This is the array.
So.
It can support.
Composite key.
Means.
Multi columns.
Yesterday.
Complex scenario.
Where there is no relation.
Employers.
Records.
In user's table.
Let's say.
2 or 3 fields.
Through which.
You can find.
So.
You have to give comma.
And this.
So.
You can find in both fields.
For now.
Simple id.
But this is to support.
Composite key.
you have to write it in the array
same now your user's relation with the applicant
I have already told you
one user can create only one applicant profile
now in the applicant's table
if you have to find out the applicant's records in the user's table
then which field will be there?
in the source target
users.id
reference means
target
which is your table
that's your applicant
what will you find out for that?
applicant.id
and what to say with sessions
one user can have multiple sessions
whenever you define many
then you don't need to give fields and references
because it happens
but when you define one-to-one relation
then you have to define the property of fields and references
fields means your source table's target
references means your targeted table
that's your applicant
where can you find out these two?
and finally
now you have to tell the relationship of sessions with the user
where one user can create multiple sessions
but can a session belong to multiple users?
I have to tell that too
so same here
relations
this is my sessions table
and again this is the helper function
inside the callback function
and now we have this user
this is the name with which I am representing
actually this is your one helper function
this is the user table
so each session belongs to one user only
means our session
one session will belong to only one user
and in that too your field
which is the target field of your source table
with which you can find out
so sessions.userid
and in references your target column
and that's your user.usersid
ok
so yeah guys this is it
I hope you liked the video
as I said
the most important thing is to understand the syntax
what are these fields doing in the syntax?
what is the reference doing?
when do you have to write this?
because here I have not written
so if it is many then no need
if it is one then need
what is the relationship?
and what is this schema?
how do we define?
it is very important to understand all this
only then you will know
what is happening in the backend
what records you actually need in the database
through the frontend
ok
so yes that's it for today's video
I hope you liked the video
and if anyone has any doubt
do comment and tell me
it takes a lot of hard work to make a video
from so many years
so guys if you want to support me
if you like it then you don't have to do anything
just subscribe
it will help me a lot
I am not going to get 1 rupee from 1 subscribe
but I will feel good
that people are connecting with me
I am helping them
I will be very happy
that's why like the video
share it with your friends
next target is 8 lakh subscribers
if you haven't subscribed yet
please do it
see you in the next video
till then guys thank you so much
take care