Sign in or start a free trial to avail of this feature.
8. Data Types
In a database, every field must have a type. This lesson provides a general overview of the common data types that are found in most databases, with an explanation of what data each type is used for.
The goal of this lesson is to learn about the types of data that can be included in a database.
Numbers may be the most common type of data in any database. There are several types that are used to represent numbers. Whole numbers are represented by the Integer type, sometimes just called Int. Other types, like Long, exist for very large numbers, but you usually don’t need them in business contexts.
Numbers with a decimal component are represented by various different types, including real, float and double. Generally, the only difference between these types is the amount of precision they support. Precision refers to the number of significant digits in a number. Again, precision is rarely that important in a business context, so you usually don’t need to worry about which numeric type you use.
There are two main data types that accommodate text-based data. The character type represents a single character, which can include a letter, a number or a punctuation mark. The string type then represents a series of characters. A word, a sentence or a paragraph would all be examples of strings.
Boolean data can have two possible values: true or false. The two possible values can be one and zero, or Yes and No, or something else, but there will only be two values. Boolean fields can also be called binary fields, or Yes/No fields.
Date and Time Data
Dates and times are common in business data, and most databases contain a Date type and a Time type to reflect this. There can also be a Date/Time type which combines the two. Dates are usually stored in the format Year-Month-Day, and times are usually stored in the 24-hour format. However, dates and times may be displayed to users in a number of different formats.
If your primary data analysis tool is Excel, then you might not have a lot of experience working with data types. Data types define the kind of data that is specific field contains, such as numbers or text, and are an integral part of every database. In this lesson, we'll learn about the types of data that can be included in a database.
Every field in a database must have a single type and all the data in the field must be of that type. However, different fields within a single table can have different types.
Let's look at some of the more common data types you'll see in data bases.
While the exact types and names can vary, the basic types we'll see here are found in pretty much any database. Numbers are perhaps the most important type of data in any database. The type of numeric data depends on whether the numbers contain fractional or decimal parts, as well as the desired precision. Whole numbers are represented by the integer type, sometimes is just called int. Integers can be positive or negative numbers without any decimal component. For example, numbers like eight, negative 53, and 10,000 are all integers. Generally, any number you use in a business context can be represented as integers.
If you work with very large numbers, you may come across other types like long, but most people will never need to use these. If your number has a decimal component, then you'll need to use different numeric types to represent them.
There are a variety of types that can be used to represent decimal numbers, including real, float, and double.
Generally, the difference between these types refers the amount of precision they support. Precision refers to the number of significant digits in any numeric entry. Significant digits are any digits in a number other than leading zeros.
For example, the number 3.1419 has a precision of five, while 0.000478 has a precision of three.
In most business contexts your unlikely to need large amounts of precision.
If this is the case, you can use these data types interchangeably.
Text based data is another common type in databases. Text data is often stored using a type called string or character. Generally, a character type represents a single character, such as a letter, a punctuation mark, or a number that you don't wanna use for calculations. A string represents a series of characters that form words, sentences and other text fields. When applying the string data type you may need to set the length of the field to determine the maximum number of characters that a string can hold. Another common data type is a Boolean. A Boolean field is one that can have only two values, true or false.
Sometimes the two possible values maybe one and zero, or yes and no instead of true and false.
In any event, there should only be two possible values.
Booleans are also known as yes/no fields or sometimes binary fields.
An example of this data type could include whether a particular product is currently sold or has been discontinued.
Moving on, we'll look at dates.
Dates are very common in business data and most databases will have a date type.
Generally, dates in a database are stored in the internationally recognized format of year month day.
For example, 2018-10-20 would represent October 20th 2018.
Databases may also have a time data type, which stores time data.
This is generally stored in the 24 hour format.
You can also have datetime data, which stores both a date and a time.
We've now seen the basic data types that are generally found in most databases. While your database may contain other data types or similar data types with different names, the types we've seen here are the most common ones you'll encounter. Over the past two lessons we've learned about tables in a database and the data contained in them. In the next lesson, we'll look at how you can join two tables together to combine their data into a single table.