Which Data Type Allows Both Positive And Negative Numbers As Field Values?

Equally a typical company'southward amount of data has grown exponentially it's become even more critical to optimize information storage. The size of your data doesn't merely bear on storage size and costs, information technology also affects query performance. A key gene in determining the size of your data is the data type you select. This tutorial will explain how to select the right data types.

What are Data Types?

Data types define what kind and range of information can exist stored in a given field (or cavalcade).

Consider a sales tabular array with these sample records:

table of sales data

Each field has its own blazon and range of values:

purchase_time: date and time of the sale sale_id: integer values incrementing by 1 for every new sale customer_id: integer values incrementing by one for every new client currency: text always in the 3-character currency code amount_paid: budgetary existent numeric values between $0.00 and $1,000.00 device: text, where the values can be: 'desktop', 'mobile app', and 'mobile web' has_discount: boolean where entries tin be TRUE or Fake notes: text, where the entry tin can be every bit long as what is allowed in our agent input tool (250 characters)

The kind of data (integers, text, real numbers, etc…) and the possible value ranges (0 to 1,000; whatever 3 characters; etc…) stand for to specific database information types.

What are the Possible Data Types?

Different databases have dissimilar information types available, merely most fit into these categories:

Numeric:

integers: for numbers without fractions. Can exist signed (permit positive and negative values) or unsigned (simply allow positive numbers). Commonly used for ID fields and counts of something
decimal(x,y): for numbers with fractions requiring exact precision. Can be signed (permit positive and negative values) or unsigned (only permit positive numbers). Ordinarily used for monetary fields. The user specifies the number of significant digits immune overall (ten) and afterwards the decimal betoken (y) in the parentheses
float / doubles: for numbers with fractions non requiring verbal precision. Can exist signed (permit positive and negative values) or unsigned (but let positive numbers). Ordinarily used for all real numbers except monetary fields

Date/time:

date: for date values

time: for time values
timestamp / datetime: for engagement and fourth dimension values

Text:

character(n):for fixed-length character strings, where the value in the parenthesis dictates the stock-still size of each entry
varchar(n): for variable-length character strings, where the value in the parenthesis dictates the maximum accepted size of each entry

Boolean:

boolean: for boolean (true/fake) values. Some databases (similar MySQL) don't have boolean data type and instead catechumen boolean values into integers (1=TRUE, 0 = Imitation)

Most databases offer size variations for each type. For example, MySQL provides these possible integer data types:

table of MySQL integer data types

How to Select the Right Data Types

The basic strategy for selecting the best data type is to select the smallest data type that matches the kind of data you take and that allows for all the feasible values of your data.

For instance, customer_id in our sample sales table is a whole number starting with 0. Today our fictional company simply has 15,000 customers. Using the MySQL integer data type table from the previous section, we may be tempted to select SMALLINT unsigned every bit the data type, since that'due south the smallest data type that will accept our electric current integer values from 0 to 15,000. Still, we look to get to 100,000 customers over the side by side 6-12 months. When we go over 65,535, the SMALLINT will no longer be sufficient. Thus, a amend option is MEDIUMINT unsigned, which should cover united states of america for the side by side several years.

You lot could allow your database select data types for you when creating your tabular array with sample records. However, this will rarely requite you the best data type selection. For example, letting MySQL select the data types for a sales table with the sample values shown previously gives several bug.

possible issues with mysql auto-selected data types

The issues we meet with the data types MySQL automatically selected are:

wrong data blazon: you won't be able to use the field in the manner that you would expect. For instance, having purchase_time as a cord and not a time information type means y'all won't exist able to perform time operations (such equally computing how long information technology'due south been since the purchase) on the field
too constrained: MySQL volition give errors when yous try to input values that are larger than what the data blazon allows. For example, we'll get errors if we have a auction with an amount_paid of $100.00 or more or an agent_comment that is longer than 65 characters
as well conservative:while nothing will break from being also conservative with information types, y'all will exist wasting storage space. With our example information, nosotros could save fifteen% of storage by using the less conservative preferred options

Equally smart as modern databases are, the owners of the data nonetheless know best about what kind of information is stored and what are the possible values the data tin can have in the foreseeable time to come. So you need to carefully specify the information types for each of your fields.

Documentation on data types for different mutual databases are provided hither:

MySQL
Amazon Redshift
Apache Hive
Teradata

Conclusion

For your database to function correctly and be as optimized as possible, it's important to carefully select the data types for each field in your tables.