Why use dice type and not just use Varchar?

Asked

Viewed 164 times

0

I am manipulating some databases in which the vast majority of columns are with the data type VARCHAR for fields such as Date, Price, Total Value, Birth, Date/Time and etc. Briefly, practically everything is in varchar.

At least the tables I had to create later I decided to put in the type of data that that column is intended because, for me, it is easier at the time of programming to manipulate this data.

But the people where I work "get on my case" because I decide to use different types of data from VARCHAR. At one point he even argued about it. They said it was silly and a waste of time.

However, I believe that there are benefits in specifying the type of data, mainly because it is easier to manipulate during programming and to occupy less space in the database (my statement is correct?).

Are there more benefits in specifying the type of data each column is intended for in a bank? Better yet, there is some kind of study that addresses the benefits of using certain types of data during the storage or execution of a project program?

database typing

  • Most of the time fields with specified type may have a certain type of optimization for that type, besides of course, if by chance and for example there is a data validation failure and there is an attempt to insert a field that has some text that is not alphanumeric in a field in the database that is with the integer type will occur an insertion error.

  • 1: source and A basic example but not far from your reality (Note the comments) In this case we can have a certain performance difference between the types varchar and char depending on the type of data you want to write in the bd

1 answer

1

I think you’re completely right in using the right types for each field. At a glance I can cite three disadvantages in using varchar for everything:

  • Decrease of semantic understanding : type helps in faster understanding of field utility.

  • Presentation of results and restrictions : depending on the type the comparisons made in queries can generate unexpected results. In the example below the result would be for ages>'18' would return Maria who is 9 years old (because of the alphabetical order used to varchar consider '9'>'18').

            create table Pessoa(
            pk_id int primary key identity(1,1),
            idade varchar(3),
            nome varchar(10),
        );

        insert into  Pessoa(idade, nome) 
        values
        ('17', 'José'),
        ('9', 'Maria'),
        ('11', 'Antonio'),
        ('22', 'Julia');

        select * 
        from Pessoa
        where idade>'18';

  • Another problem would be the use of grouping functions such as AVG and SUM that would not be possible to calculate values from varchar.

I believe that there are even more problems beyond those I mentioned, if you can read on Five Simple Database Design Errors You Should Avoid, some similar problems that occur during bank modeling.

Browser other questions tagged database typing

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