4
I have a column guy with 6 options to save in the bank, it is better to save the string as varchar or as int and treat it in front? For example, when it is 1 in the bank, display certain string on screen and so on.
I know that int would have more performance, but would like to know if have too much headache or risk of inconsistency. I thought to save as varchar even so I don’t have to keep treating it.
3
You can’t say without understanding what you really want, without knowing the whole context.
I’d say it’s better to wear one int or even a smaller integer type since there are only 6 options.
I don’t think it is desirable to have the text space occupied in the table. But the worst problem is if you have to rename one of these types. It may be useful to do this.
This is considered an enumeration and some databases have their own way of dealing with it. But it is common to be more advantageous to have a normalized table with the descriptions. Of course the normalization generates some extra cost, but if everything goes well it will be in the cache and the cost will be very small. But it may be exaggeration to do this.
The problem to deal with in the application is that if you need to change a name, or add a new type, you will have to deal with it properly, not everyone knows how to do it correctly. But it is far from wrong. It may be the simplest solution.
Risks always exist in each of these solutions. If you do wrong any one may not work right. But none have obvious risks even doing right.
Take a look:
I get it, it’s pretty clear now. So I’ll use a table with the description, but how am I going to use this more often, will many tables with descriptions hurt performance? Is using only int and treat in the application considered bad practice? Taking advantage, in the other post you said not to use Boolean even if it is 2 values only, in which scenario should use Boolean in the comics? Thanks.
No, too many tables don’t hurt. There are those who like to create a single one with all description, then there would be 3 columns, one saying what kind of description it is, another identifying the description and the third that is the description itself. I have done this in the past because there were various restrictions, today I see no need to do it anymore. There is nothing bad to do in the application, just do it right, but this goes for any solution. This not to use boolean depends on the case. Only use if really values without YES and NO and will never change, if it is something else, do not use, sex case.
Okay. Thank you very much, @bigown, you helped a lot!
Browser other questions tagged sql database typing
You are not signed in. Login or sign up in order to post.
You can use type as ENUM or SET
– Williams
Normally use
tinyint().– Daniel Omine
@Cool joker, didn’t know these guys, I’ll take a look, thanks. Daniel, thanks for the tip.
– Fernando Zabin