Which attribute to use for database fields working with numbers from 0 to 5?

Question

Needed to mount a table in Mysql, where the values recorded in this table will be between 0 and 5, what attribute types within the table field should be assumed? What kind of data do we report in the column tipo_DADO?

Apparently the question of the concise type of data in the creation of the table was not addressed, but in the insertion and capture of this information from the table they are treated.

CREATE TABLE IF NOT EXISTS minhabase (
id int(11) NOT NULL,
nome text NOT NULL,
dia text NOT NULL,
cpf text NOT NULL,
typedor text,
outra text,
eva text,
idor text,
cp text,
lo text,
cam text,
sen text,
fp text,
dm text,
vse text,
vsoc text,
loc text
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=cp850;

Answer 1

Looking at your modeling I see some weird things. First I will answer what was asked.

And the question is a little strange too, maybe because you don’t know the correct terms. Or we’re not understanding your real intention. Attributes in the background are the columns. People’s interpretation is the same as mine and I think you want to know what kind to use. In fact it seems that you are confused with the types (which at the bottom approximately is what is usually called domain in modeling) to be used in all your columns.

Column type

As already commented if you know that the possible numbers will be between 0 and 5 and can only be integers, the ideal is to use an integer with the smallest possible storage space. You can even use one that allows you to store numbers with higher values but is waste. So just have a type of 1 byte which allows to store values between -128 and 127 since each byte allows representing 256 different values.

So just use the type tinyint. In the table definition can establish without fear a type tinyint(1). This 1 determines how many digits will be shown when you make a query and in case you only need 1 digit. So a few examples of what it would look like:

eva tinyint(1)
idor tinyint(1)
cam tinyint(1)

In theory you could guarantee that only values between 0 and 5 could be written in these columns (or attributes as they are formally called in the templates). By default Dbms have the ability to do this restriction via keyword check but in Mysql this is ignored.

Alternatives

Another possibility is to use an enumeration. It may be that these values from 0 to 5 have a specific semantics. That is, it may be that these numbers are more than numbers, that they identify something with greater meaning. In a way we can understand as if they were references to an auxiliary table to indicate what this is. With the enum you can create a meaning for these numbers and ensure that only they can be used.

I’m not saying it should be used, just that it’s an alternative if you realize it’s more suitable for what you need.

Other columns

Moreover, it is not possible to identify well what each column is. The names of the columns are strange. Maybe they have meaning to what you’re doing, but maybe you just want to keep them short. If so, it’s not a good idea. Giving meaningful names is very important.

The column dia is it really a text? Is there any chance of it being a date? If it is, use the type date or datetime.

The column cpf would be the person’s social security number? So the guy tinyint doesn’t make sense. If it’s CPF it should probably be a varchar(11). Data that serve as identifiers should be text even if they only have numeric digits. Another detail is that you should not write formatting, the use is, the points and dash of the CPF, the data is just the composition of digits. See CPF or CNPJ field type in VARCHAR or INT database?.

Since you have these apparent errors of types, it would be nice to review the other columns.