MDBitz Development Resources

Recently at work I have been working with Database Tables that have been denormalized to “enhance” their performance. As part of the denormilization process table columns where converted from int to the varchar data type. This change lead me to wonder if there is a performance gain from using varchar over int.

After doing some research on this subject it turns out that int columns provide both performance and data integrity.

1. varchar takes longer to compare
   Due to the varchar type allowing the storage of character data a varchar type takes longer to compare then an int value that uses only numeric values
2. varchar removes type check
   Perhaps the greatest reason for not changing the type to varchar is that if you are expecting the column’s value to be an integer then the int column will do a type check that the value inserted is an integer. However by making the column a varchar this check is removed and your column is open for invalid character data that could cause failure to any sql scripts that do conversions.

To wrap things up, lets keep it simple if you need to save a value in a table that is an integer then use the int data type.

When designing the Database Tables for an application there are many factors that come into play. Often one issue that seems to crop up is the use of denormalized vs normalized tables for representing data. Below is a brief overview of what normalized and denormalized tables are. As for which one to use in your own application that is up to you with many factors coming into play including speed, extensibility, and limits.

Normalized DB Schemes are when you rationalize data objects into tables to reduce and avoid redundancy. A sample would be the below schema where you have a person table, phone table and phone type table. With linkage between the tables to associate the data.

Having a normalized database would allow for a person to have any amount of phone numbers of every type. However if this structure was denormalized then this information would be condensed into a single table that would contain all this information. When doing so it would have to be decided how many numbers a person can have and of which type. One possible denormalization would be to only allow 1 number of each type (home, work, cell, fax) giving the below table design.

As previously mentioned this denormalization of the data limits how much information can be stored, however it does increase performance of the database in certain as you do not have to do joins for basic select statements.