How to determine the most suitable data type for a fixed-size numerical field?

Asked

Viewed 221 times

1

I’m having a doubt about the characteristic of some attributes of my table.

Problem: I have to keep an identification number of my items, this is always composed of 3 digits (zero left in case of numbers below 3 digits). I can record the final number literally in the database, just as I can record by ignoring 0 (zero) on the left and treating it in the application. I believe that to record with zero left I will have to use the data type char(3), already to save the whole number I must use smallint(3).

Note: consider also that the types of data mentioned consume:

  • smallint = 2 bytes
  • char = 1 byte

Considering that this is an ID number that has no relationship and will not be used for calculation formulas, there is some rule that determines the type of data most suitable for this column, still considering performance (in my performance context is a priority) and Storage?

Note: My environment is composed by a Laravel 5.6, php 7.2 e MySQL 5.7

php mysql database laravel

  • if you want to record in your base 001, 022, 333 ?

  • If you want to fill left zeros in an entire field use zerofill, cannot use auto_increment in text columns

  • @Virgilionovic this, however, the problem is not in generating the numerical sequence. But determining the best type of data in the bank. I am in doubt between considering the char type and already sending the string "002", or considering the smallint type, send the integer number and in the application do the processing to insert the zeros to the left. Doubt as to the approach itself, how to do it is simple, I can not use zerofill because the bank can change, I’m trying to work only with what is allowed within the Laravel Migrations to structure the tables.

  • 1

    I would save as whole and then deal with your formatting in the application. in Laravel this would be handled by mutators in Laravel

  • Honestly, your question can be considered to be based on opinions, because, if you can do it both ways,not compromise on any performance, even if you have difference in bytes. I would do as I told you, but if you put char with 3 positions is also correct

  • If the goal is performance, why not save as whole and treat the return?

Show 1 more comment

1 answer

1

In terms of performance whatever, it will change nanoseconds into something that should take milliseconds.

You’ll get more performance tuning the database than doing it.

The account of the occupied space is not always so simple, and will change very little for you.

If you want it to be incremented automatically, it can only be numerical.

If the code is a text, the right one is a char(3) and would have to administer the increment.

There are those who give a solution using both, one to increment and be primary key and another to show the code as it should be.

Browser other questions tagged php mysql database laravel

You are not signed in. Login or sign up in order to post.