The Deal With Data Types

When you’re setting up your database it is important to take the proper steps to ensure your data is clean, accurate and efficient. Every column you create by necessity has a data type. They help whatever database agent you’re using better understand the data that is recorded and help determine how to interact with that data. Every SQL client varies slightly with the names and functionality of their data types, but for the purpose of this blog I’ll discuss those associated with the material being covered here at Flatiron.

String

Probably one of the more common data types due to it’s versatility. Good for short collections of text, numbers, and characters. Fields with this data type won’t exceed 255 characters.

Text

If you need more than 255 characters, then text is for you. This is perfect for long strings of various characters. This includes basic paragraphs, or even large chunks of code as well.

Boolean

Boolean datatype can be incredibly useful in situations where there is only two options. While it only holds true or false, this can translate into yes/no, show/hide, and many other paired relationships.

Binary

Also referred to as a blob data type. Binary is measured in bytes rather than a length of characters like string. It’s good for storing compact pieces of information like media and other files.

Integer

Integer holds only singular whole numbers. Much like the string, it’s one of the more common data types.

Float

Stores numbers with the decimals, but limits the byte size associated with that column. Often numbers get rounded in order to meet this criteria which makes calculations not quite as accurate.

Decimal

Much like float, it holds numbers down to a specified decimal point. The numbers are much more precise thus are better to use when attempting to make accurate calculations.

Date

Stores only the date and ignores the time. Formatted with the day, month and year.

Time

Stores only the time and ignores the date. Formatted with the hours, minutes and seconds.

Datetime / Timestamp

And given the flow of this blog you can probably guess datetime gives you both the date and time, fully formatted with seconds, minutes, hours, day, month and year. Timestamp does the same thing but with Active Record’s help we can use it to make two additional columns, created_on and updated_on.

class CreateCostumes < ActiveRecord::Migration
def change
create_table :costumes do |t|
t.string :name
t.string :size
t.string :image_url
t.timestamps
end
end
end

With that simple syntax we are able to create two auto-updating columns that help us better understand and organize our data.

In summary, data types are pivotal when setting up your database. The actual types may vary depending on your database management system. It is important to familiarize yourself with all of the available types to give an accurate representation of the data your application persists.