What attribute do you use for database fields that work with numbers from 0 to 5?

Question

What attribute do you use for database fields that work with numbers from 0 to 5?

Navigation

#1 by (17 votes)

-2

I needed to set up a table in MySQL, where the values written in this table would be between 0 and 5, what attribute types within the table field should be assumed? What kind of data do we report in column tipo_DADO ?

The issue of the concise type of data in the creation of the table has apparently not been dealt with, but in the insertion and capture of this information the table is 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;

mysql sql modelagem

asked by anonymous 10.03.2015 / 08:09

1 answer

Pass dynamic SVG elements to file PHP Query Error

score 17 · Answer 1

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

And the question is a bit strange too, maybe because you do not know the correct terms. Or we are not understanding your real intent. Attributes in the background are the columns. The interpretation of people is the same as mine and I think you want to know what kind to use. In fact, it looks like you're confused with the types (which are roughly what's called a 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 larger values but it's wasteful. So just have a byte type that allows you to store values between -128 and 127 since each byte allows you to represent 256 different values.

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

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

In theory you could guarantee that only values between 0 and 5 could be written to these columns (or attributes as they are formally called in the models). By default, the databases have the ability to do this by using the check keyword, 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 them as references to an auxiliary table to indicate what this is. With enum you can create meaning for these numbers and ensure that they alone can be used.

I'm not saying that this should be used, only that it is an alternative if you realize that it is more suitable for what you need.

Other columns

In addition, you can not pinpoint what each column is. The names of the columns are strange. Maybe they have meaning for what you're doing, but maybe you just want to let them short. If this is it, it's not a good idea. Giving meaningful names is very important.

Is the dia column a text? Is there any chance of being a date? If so, use date or datetime .

Would the cpf column be the person's CPF? So the type tinyint does not make sense. If it is a CPF it should probably be a varchar(11) . Data that serves as identifiers must 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 digits.

Since you have these apparent type errors, it would be good to review the other columns.