#3: INT, VARCHAR, or TEXT❓ MySQL Data Types Explained🔥Next.js Job Portal Project

Hello what's up guys, today is day three of the job portal project, yes guys, before this we have seen two videos where I told you that first of all brother, do not sit directly for coding, let us do a little planning, understand which users can be there on our job portal application, what functionalities can be there, what features can be there, we did all those discussions together, right, once this part one was done, then after that we made part two where I told brother, wait wait, we still do not have to do coding, let's start with the database and we have to understand which fields can be there in the database , which tables can be there. Right? So we completed this in the second video. So today in this video I'm going to tell you that we made tables that day. We created Guy's Fields. I gave you a task that day. And that is, now whatever fields you create. Right? Each field has its own data type. Isn't it? Each field has its own data type. Let me show you. As you can see here, each field has its own data type. Like I have kept int for ID. Ware care is taken for the name. For you know role, user role is enumerated. We have kept the URL of the avatar in text form. Two, the string written here is not from My Sequel but yes, it is useful somewhere in ORM. So right now I just want to show that today in this video I want to clear the doubt regarding this data type. Be Leave Me is one of the most popular videos. And once you complete this video, then guys, you can create this type of entity relationship table very easily. Ok ? So guys, let us start the video without any delay. First of all you have to understand why should we study data types? What is so special about it? After all, we know that if there is any number, then paste the end. Any Ah character, any name string, then paste the ver. If it is something time related then paste the date or date time. Brother, it is not that easy. I will tell you three such things. In the why part, you can see here why it is important for you to understand the database. So the first of those three points, guys, you can see, is data integrity. So this data integrity is all about your data accuracy and consistency. What does this mean? Like if we have this here, you can see very clearly we have this ID. Isn't it? So, this is my ID, I would like that in this ID field of ours, only number should always be stared. Only the data type containing numbers should be stared. There should be nothing else apart from that. If you put the string hello here, it will be wrong. So, this means that this data type here is the first step of your validation whether you are inserting correct data in your field or not. So data type is basically data integrity multiplier. So guys second is your storage efficiency where if you know I have a field in our job portal application which is going to take very less space. Right? So there if we use tiny int. Don't worry, next we will read what is tiny int and all those things. So it takes only 1 byte of our memory. But if you had given Big Int instead of Tiny Int as a data type, then it would have taken up 8 bytes of your memory. So let's assume your row had like millions of roses. There were millions of such fields. So if you give big bricks instead of tiny bricks everywhere, then think how much storage you are wasting. If we look at it another way, if you had used Tiny Int, you could have saved gigabytes of space there. So this storage efficiency is also very important. So third is guys your performance. This simply means which data type should be given to which field. If you do this well then guys the performance of your application will also become awesome. So I hope you understood these three things. Ok? And here you can play with it a little bit. Like if you compare size, how much does it take you tin int ? Here you will know how much the int big int back care character takes. I will provide the link to it in the description and comment section below. My own company, Codefire, is this. Ok? And if you want online classes of Mysquel, recorded videos which we have taken, then please comment in the contact and write class class, I will reply to you there whatever happens. Now guys now we actually see data types in my sequel in which we have a numeric types string types your date and time and then we have a balloon. These are the four types. But you can say all the categories within it. Like in numeric we have a tiny int, we have an int and then we have this big int. This is very simple. You can understand it like this. So here Tiny End's is very simple. Tiny means a small integer, meaning in our job portal application, there may be a field in some table where very little storage is required. You are going to use very few characters. In cases like between -28 to 227, if you feel that your work will be done in this much, then guys, you can use this tiny int there. Like if 'is active' is true or false it is either zero or one, so there you will use this tiny int very easily because what it will be is zero or one, whatever it may be, it can be a right or status flag and small category like job status zero, one or two, then there we will use tiny int and if you add unsigned like a tiny int space unsigned as it has been done here, you can see that if you unsigned after int, then your storage becomes 0 to 255. Unsigned means that it will never allow you to enter a negative value, which is awesome. So now guys second we have this integer. Here if you think that my range is up to 2 billion characters, whatever you call it, right? If you use unsigned then it doubles to 4 billion. Ok? So you can use integer there. For example, if you have any primary key like your user ID, your job ID, your company ID, then we can use this int there. And a very important pro tip you can see here. Always use u no like unassigned for ID columns. Because unsigned will make sure that there are never negative values. Have you ever seen user ID-2-3? No, right? So you must use this unsigned. Ok? Now in third we have this big int. It is very simple. If the range is small then Tiny Int, if it is medium then your Int. If you feel that even integers are not going to work for me. My range is very high. So there we use big int. And then again we have this unsigned here also. So you can understand that the danger number is being discussed in massive trillions. Ok? So as you can see your transaction ID here. You will see this very rarely. But whatever it is, it is important to know this. If it is small then it is called Tiny Int, if it is medium then it is called Int, if it is very big then it is called Big Int. You have to keep this simple method in mind. Now you have understood, right? We always used to write only int but now you know the range of at least tiny int. We found out the int and we found out the big int. Then guys second we have a string data type. If you look in the string, we have a wherecare text and then character. So like when we talked about int in numeric type, then tiny int, int, big int, this is an ascending order. Similarly, if you look at the string, its ascending order will be the character. Basically care where care and then text. Ok? Now it is very easy. Listen carefully. Let me tell you Carr first. Alright guys so let's start with Carr first. Ok? A character is a fixed length data. Where it is clear to you brother, this is my field. Ok? There might be a field in my table where I'm going to need either a two character or three character maximum. So you can use a car there. For example, the most perfect example is country code. Any candidate may be from different countries. So in the country code I am USIN so how many characters am I getting? Two characters are being used, right? So country code carry two I can write it like this. And make sure that these two characters are written, you can write only two here. Although you can write less than two but not more than two. Then guys second we have this were care. This V in this is basically not V. This VAR is your variable character. What does this mean? This is very interesting. I'll tell you right now. What is written? Only uses space for actual characters stared plus a tiny overhead. What does this mean? Let me tell you. This is a very important analogy. You will understand here. Ok? So guys, here you can see that you can see two in the car. Ok? Similarly, if I go over here, I could write Carr 255 like this. Now here I wrote 'Car 255' and let's insert the value and put it in this way. Ok? So when I wrote this Thapa here, how many total characters do I have? So here, my total character count is five. But here I have said so many times that brother, please give 255 to this field. This would mean that I am only using five characters but it will take up my entire storage there, which is not a good thing. Right? And if I remove this from there, now if I talk about vy because many of you must have seen that whenever vy is mentioned, so vy care, we write 255 like this, right, and let's say again my value here was Thapa, I entered the username Thapa, now the beauty over here is this ware, it represents, I mean, it means a variable, so a big advantage here is that we are just writing Thapa, but I have said 255, so what will it do in our data, only and only, total five characters here, so five, oh, there is some overhead, let's take it at the most, 1 byte, that means 6 bytes, that means in my database, it will take only six bytes of, I mean, of whatever memory I have, only that 6 bytes of storage, I mean, of whatever memory I have, it will take only six bytes, this is the beauty, if you had used care, it would have taken up the entire 255 bytes, so what good is this? Obviously your wear care is good in both but it does not mean that you should write small things like US everywhere, you know, it is clear here brother it will be two only, so if you write care two then it will become faster, here it was very important to understand the difference between these two that you write 255 two, but usage, I mean only that much space will be taken in the database as per your value, okay this was very important, now where is its use case, guys, if you say the user's name, your job title, your email ID, you can use it there, even your phone number, password there, you can use it there. Now the text is much simpler. You must have noticed that the maximum limit of where car is like it is 255 characters. Now you are thinking that no brother, I need more than that, then directly go with text simple. Isn't it? Now, just like you have a job description, user's bio information, if someone is commenting, if someone is writing a blog, then you will get text there. Isn't it? So it is that simple. Right? Now look here. Use the Wear Care vs. Text rule for a predictable length under 500 characters. You think 500, let's leave 255 there. So, I am saying that even if there are 500 characters, if you feel that you can make a prediction that whatever this field is of mine, its value will be this much. So use very careful there. If you feel like it could go on for too long, anything could happen. So there you have to use text for string. Keep in mind that here we are talking about the string one. And here guys you can check get recommendation based on your data. Like what have I written, whatever I have written, I have written it. So this is showing the total length as 26 characters. So were care 100 good for variable text. You can see that in this way, basically I have created a website of this type and kept it here. You can check this out. Now the third one is your date and time data types. Now this is very easy. Like a date. Now you should get the idea immediately from the date. Like our date, I mean not date like date of birth, what is that? It's a simple date, so you can use it there. Stores only provide up to date information. If you want information about empty date then you can use date there. Now there is an event where you also need a date. If you also need its time, then in that case we will use this date time data type. And you will get its value like year, month, month, date, hour, minute, second in this manner. Like there is a job post in our job portal application, when any employer will post a job, then on which day it was done, at what time it was done, we need both date and time, so this is a very perfect example there. Whereas if it is your date of birth then it is a normal date. Now what is the advantage of this time stem ? So look, this time stamp is also like your date time. But one advantage here is that guys, this keeps getting updated automatically. And it converts to UTC for storage and back to the local time zone for display. This could mean that basically you, the user, created your profile. After 2 days he felt that something went wrong here. I have to correct this. Meaning the user had updated his profile. But after to does again she updated. But I need the data about what he updated first. Then again when did he update? So that part where it is updating the date and time, in that case we always write timest time. And the beauty of this is that if you set it to the default current timestamp, something happens. If you write that and leave it, you don't have to do anything. Like whenever it gets updated, this value will automatically get updated to the current time. Rest let me say that all this is fixed. Here it is a fixed event. Just like my date of birth is fixed, that is fixed. Date and time like the FIFA World Cup 2020 which is going to happen next year or whatever happened before, that too was a fixed event. Isn't it? When is the FIFA World Cup, whatever it is, I don't know which one, I forgot. Sorry, on which day and at what time did it start? That is fixed. We can never update that. So the date time will come there. Guys, this time stamp can be used in many places. Like the user is changing his bio. Someone is writing a comment. Then now he is updating it again. He is changing. Basically, wherever the edit option comes for changing, you can use it there. Ok? For example, this is best for created at updated at columns. So now finally the last one is your bull type in which you write true and false values. No doubt like it is active is remote. In that case you can return true or false. So you can write here in two ways. The number one is like is remote. You can either write Boolan and else is active tiny int one. Even if you write a boolean, behind the scenes when that data is being stored, it is being done as a tiny int. Ok? So you can write anything from both. Here, when you feel that the output is going to be either zero or one or true or false, then write this boolean in the fields of that type. Your work will be done. Ok? Is active is remote email verified or not. So here you can see try toggling this balloon values is active. So look, you will see the value in this way. As soon as I turned on Easy Remote, it became true. So here you can see that this value has become one. Let me delete this zero let me delete this. This is again zero falls. There is an example of this. You can see it here. If you have any doubt on this then look at this example carefully. When you are creating, you can also write this like this: Ease Remote Balloon. Ease Active Tiny Int You can also write this. Bullin if you're writing False True is something like this. If you write tenant then you can write one and then zero in this way. Ok? So these are your data types. So ya guys this is it. That's all for this video today. Before I go, if you have noticed a pattern, let me tell you. When you are playing with integers, you are basically getting three data types in integer. If it is the smallest amount then use tiny int. If it is medium then use int. If it is too much then use big int. Even in stringing, your care should be used for minimum to medium. If the maximum is very high then use text. Both of these are fixed in date and time. If it is dynamic and changes are taking place then use time stamp there. I have already told you this. And many people have doubts about this where and text. I hope that has cleared up. Even the difference between 'care' and 'wear care' must have become clear. You also got to know how useful the ware variable is. Tiny int here, big int here, you get it. And most importantly, don't forget to use this unsign here. Whenever you are assigning IDs because they can never be negative. Isn't it? So you have to keep all these things in mind. And I would like to say this again guys, what we saw in this second video is our data type. Ok? You basically created all the data types that we created in that video. Now finally I have uploaded this video. So now guys, I think basically our second video that day, we made it this way. So now you can very easily, like your resume, this is your int, in that also your unassigned is in this way, so now you can do whatever their data type is in this way. Ok? Please fill it if you have not done it yet. You can make it this way. What could be the file size ? Isn't it? There is a URL. So what did we read about URLs for? There is no doubt about the string. So what will happen there? There will be Pay Text or Wear Care. So now you must have got the idea of ​​I Guess. There is still one thing left here. And that is the relationship like whether it is primary key or foreign key. I will tell you that in tomorrow's video. And in tomorrow's video, I will also tell you about this part, this entity relationship, which you are seeing here, and what is each one's role. One to one, one to many, I will tell you everything here. But yes, it is very important to understand data types. Why did I tell you those three things? Data integrity, storage efficiency and then performance is what you need or not, awesome isn't it? So ya guys this is it. That's all for this video today. I hope you liked the video very much. Please guys if you like the video because I am working very hard. Please like the video and share it with your friends. And the next target is 8 lakh crore. Have n't subscribed yet, please do so. See you in the next video. Till then guys thank you so much. Take care. [music]