How to Track Who Applied for Which Job? (Next.js + MySQL + Drizzle)

Hello what's up guys Welcome to my sequel Job portal application And today is our 85th day And today we're going to explore the applicant table schema and the result schema So basically this is now You can see When I click on Apply Now The resume I've already uploaded Appears here by default This is amazing And here you can write any cover letter I mean by writing a cover letter I can code anything—anything at all —and just click apply now So the moment you click apply now your application is submitted successfully You as a candidate You cannot apply for the same job multiple times That's why this button is disabled And if I go here Now if I refresh it You can see an ID displayed here You can clearly see two numbers here—because I've removed one otherwise the applicant wouldn't be able to apply for the same position So this structure I mean this table— ID Job Applicant ID Resume Cover letter Applied at Applicant Whatever it is we have to create it in this video Basically schema And we have to generate Migrate it and send it here and then we'll perform job validation—this is covered in this video because we've gone over it multiple times so both tasks will be completed here Tomorrow we'll look at server action After that we'll see how we can build this model and then implement the functionality Over the next 3 to 4 days you'll be able to apply it By default you can view the resume in the dropdown And I think nearly 80% is going to be finished so it’ll be fun—let’s get started with the video If you haven’t watched the previous one make sure to do so And our next goal is 800 000 subscribers If you haven't subscribed yet Please do so Because your support inspires me to upload more videos So go ahead and watch I'm heading over to my VS Code editor And now what we need to do is create a schema Let me explain—this is your button When the user clicks it by default the resume they uploaded will appear If you remember here is the profile form When he fills out the form Here is the resume We've already handled this When we start looking for a job By default He can see all available positions So how does he see them The second key point When I click on it the question arises: how do we know which applicant applied for which job and which resume they used That’s the thought that comes to mind—how do we know which applicant applied for which job Because The tables we have created Resume Not only the applicant but all applicants' resumes —you might not know this employers applicants users and jobs So where is this table or what is the way to know which applicant applied for which job How do I know for sure We need to create a new table—the job application table— with the following fields: ID Job ID and Applicant ID Third number Job ID 13 And then we have this resume ID Now I know everything It is in all relations Your job application table Is a junction table Which connects multiple tables As soon as we created the table our doubt vanished instantly Now we need to build it—let's get started here: Job ID Applicant ID Resume ID Cover Letter This is it Let's go here I want you to pause the video And try to create it yourself Because we've seen this many times This is your Job application The name of the table is Job_Applications That's why you can see it clearly—we're creating a MySQL table and the first field is your ID Then we have Job ID This job ID serves as a foreign key We are establishing a reference from our Applicant table to the Jobs table where the ID field is being referenced You can create a reference From here we're using onDeleteCascade which means this table is tied to the job ID Yesterday I applied for a job as a full stack developer and also as an instructor I needed two vacancies and I managed to secure both I deleted it If I delete the vacancy it becomes useless If a job is deleted delete it as well the same applies to our Applicant ID which is the field name—we are referencing the Applicant table and this is solely a foreign key We are using onDeleteCascade Meaning as a user Let's say You're watching this You applied for a codifier For a full stack developer role As an instructor Okay Yesterday Where did you apply from Your job hotel application You deleted your account If you're not there What will your applicant do If he isn't a user He blocked his account Or deleted his account Means Why would he do that We'll remove him His data will be deleted from this table when we refer to the resume ID Here you can see the resume ID which serves as a reference and acts as a foreign key—there are three foreign keys in total Restrict means Tomorrow In case someone Said Resume ID Let's say his resume ID is 5 Delete 5 Resume ID 5 If it is deleted From our field This data will not be deleted As per policy If you submitted information for a job application Uploaded your resume And applied for a position Even if someone else Deleted your resume It does not mean The job application is invalid For which you applied If you used that resume You cannot delete it Here it is—if he’s not a job what will he do If he’s not a job then he’s not a job If he isn't the one who filled out the form then he ran off to America—what could he possibly do now There 's no point in calling him anymore But the person who submitted the resume—that's someone else entirely Someone else deleted it He is waiting I uploaded the resume When will I get a call That's why we don't want to delete it You saw something new Cover letter You can say Do you want to add some extra information When was it applied One more field: Status What is the status Did the employer see it Is it pending Rejected Later on You can keep it When did you apply Default now And When you work on drizzle You must define the relationship If you look here 101 relationship That is a one-to-one relationship One job applicatio Belongs to one job One job application Belongs to one applicant From the same resume If you think this way It will be incorrect What is being conveyed here One represents One foreign key Second foreign key Third foreign key We're discussing records A record refers to If you look here Yesterday Someone else must have entered it Someone else will handle it 2 3 4Basically The connection we established Is grounded in this record Meaning In my job application There will be numerous records There will be extensive data But if I Capture just one row If I retrieve one job ID it belongs to a single applicant and I' ll have that applicant 's resume Do you understand what I'm trying to say When I refer to one row I mean It will belong to one job there can be many job applications but when I talk about one row it belongs to one job The data belongs to a single applicant who must have submitted one resume Do you understand This is here —the record of your job application belongs to one job one applicant and one resume Here we have defined the fields and references If you see On the opposite side a single job can have multiple applications meaning multiple applicants can apply for one job and the same person can apply for multiple positions Here it represents a many- to-one relationship but in this case it applies to a single record We've mentioned this way—this is your table the schema is ready now you need to do: whenever you create a schema you must run npm run Basically it will generate your MySQL schema you need to press Enter and once it 's created you must migrate it so that your table appears in the actual database You need to generate and migrate it once that's complete the next step is to validate the data before storing it—our current approach relies on Jod validation It has a simple schema: Job ID Resume ID and Cover Letter I'll explain why If you come here it disappears again I'll delete it and refresh the page Let's say an applicant Wants to apply for a job As a senior full stack developer Here you can see the resume And the cover letter Here are the resume and cover letter And he is clicking on apply I can code anything I've written something here I need to verify this section And I've made the necessary adjustments If everything is accurate Then it will be saved In my database In the table we just created You can see It's the same data—only the ID has been changed everything else remains identical Job ID Your job ID Applicant ID Resume And here we have a string Job ID is a number Resume ID is a number At least one must be provided By default If none is specified Go to settings form Go to the settings page Upload your resume And relax You don't need to do anything else Automatic retrieval At least one A cover letter is optional You don't need to do anything Just the resume We're satisfied with the resume We'll know the details will be there you can fill them in if you'd like we've kept it optional this is our schema we're using TypeScript so we have to specify its type Drizzle helps you by using an infer method —no matter the type we’ll show it we’ve seen it this is it I hope you like this video Tomorrow we’ll write server actions How to store user applications in our database then the model pops up—by default resume is enabled and by default when we apply here's how to disable it After all this it will be fun—within 2 to 3 days That's all for today I hope you enjoyed the video Leave your questions in the comments Explore the playlist If you liked the video Give it a thumbs up See you in the next one Thank you