2
I created a table user with the following values:
Tipo = `MyISAM`
ID = `int, PRIMARY, AI`;
Usuário = `varchar, 16`;
Senha = `varchar, 16`;
Email = `varchar, 100`;
Are these values recommended? Insurance?
1 answer
12
It seems to be talking about the structure configuration of a table and not the values of a table. The correct concept helps to understand the problem.
You need a lot more information to be able to tell for sure if it’s a good database structure for your case. I will try to be objective according to my experience, based on what you reported.
The ID seems to be correct.
The others seem to have the type varchar appropriately. A kind of varchar in Mysql allows texts to be stored with variable size between 0 and 65535 bytes (which may not mean 65535 characters if you are using UTF8, for example). Essentially only the space really needed for the individual text is consumed in the table. So normally because of space it doesn’t make much sense to set a limit for the column. It actually has a limit that is 65535. Of course in practice you can’t have a column this size because this is also the limit for the row size.
Why put a lower value? Maybe because you’re sure you’ll never be or never be able to exceed that limit.
But if you are sure that a text has a fixed size, maybe you should use the char. If you want to set a limit that cannot be exceeded, you are using the database structure to perform validation. It works, but it’s not always the right thing.
If you are going to use this type of validation make sure your application knows how to handle an error generated by violating this limit.
Of course this validation can be done in the application as well and it is probably a good idea not to try to record something that is known to be in invalid state.
The sizes to Usuário and Senha seem small, but you can answer better than me. Where do these data come from? Do you have control of how this is registered? There is no need for larger size? If you do not have absolute control over the size, it is better to leave a larger size, maybe 255.
The password will be recorded as? Plain and simple? Do you know the implications of this? You won’t at least use the function password() to generate a hash of password? If you are going to use this, 41 bytes will be required. But you will not use any other method? It may be too short. Read the text that Bacco linkou in the comment. If you know that the password size is fixed (hash), the column need not be varchar, but there’s also no harm in being. Some people tend to think that one guy has more performance than the other, but this may not come true in the real case.
Email address usually has a few dozen characters but it is possible to have 254 characters. I would easily put the size 254 for this column, nor would I think of smaller size. Information can be obtained from RFC of the SMTP. 100 should not cause problems (until it does), in fact many providers do not even allow addresses that exceed this limit, but if you want a robust solution that solves the problem, leave 254. Put 100 is opinion.
If you need more detailed information than this, it would be good to edit and provide more details in the question.
-
Just to clarify some information, in the registration system new users may have: Username between 4 and 16 characters, Password between 8 and 16 characters and Email up to 100 characters, otherwise they receive an error message during registration. My goal is only to avoid failures in the future.
-
2
Ok, if you guarantee this through the application, then you can use these limits in DB without problems, no need to leave more space than can be registered. The limit can be used to ensure that the application does not pass by mistake. Which in this case would be a programming error that needs to be fixed as soon as possible, so no special treatment within the application needs to be done at all. You can’t predict everything that will happen in the future, you can just minimize a few things. You seem to want to record the pure password. Then you may have problems today.
Browser other questions tagged mysql database modeling
You are not signed in. Login or sign up in order to post.
I have doubts whether this can be answered objectively. You did not put values there, you seem to be putting the definition of columns. If that’s what you want to know,
varcharIt seems to be appropriate, but it is possible that it is not, it depends on each case. As for the sizes, it depends, do you have control over the user size and password that can be registered? If you have, you say if 16 is enough. If you don’t have it, then you better put a much higher value to ensure it. The email you probably don’t have control over. 100 may be enough but I wouldn’t risk it.– Maniero
I find too short a password of these, and user too. And since you will use varchar, leave the email at most (255) per guarantee.
– Bacco
Remember that
varcharIt only takes space really used, so why save? Setting limit is only to "protect" the column from receiving larger data. But your app probably already does that, doesn’t it? If you fail to write to the database because you have exceeded the limit, will your application handle it properly? Setting a limit is more a validation than a way to organize space in DB.– Maniero
NOTE: Said short password, because any decent hash will take more than 16 bytes. I hope you are not thinking of storing the password "clean". Read more here: http://answall.com/questions/2402/
– Bacco
I don’t know if "putting everything to maximum because it’s varchar" is a good solution... After all, varchar uses some resources to make this decrease to the size of the entered value.
– ptkato
@Patrick the Varchar has overhead 1 byte for sizes up to 255, and 2 bytes for sizes up to 65535.
– Bacco