As a typical company’s amount of data has grown exponentially it’s become even more critical to optimize data storage. The size of your data doesn’t just impact storage size and costs, it also affects query performance. A key factor in determining the size of your data is the data type you select. This tutorial will explain how to select the right data types. Data
types define what kind and range of data can be stored in a given field (or column). Consider a sales table with these sample records: Each field has its own type and range of values: purchase_time: date and time of the sale
sale_id: integer values incrementing by one for every new sale customer_id: integer values incrementing by one for every new customer currency: text always in the 3-character currency code amount_paid: monetary real 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 can be The kind of data (integers, text, real numbers, etc…) and the possible value ranges (0 to 1,000; any 3 characters; etc…) correspond to specific database data types. What are the Possible Data Types?Different databases have different data types available, but most fit into these categories: Numeric:
Date/time: date: for date values
Text:
Boolean:
Most databases offer size variations for each type. For example, MySQL provides these possible integer data types: How to Select the Right Data TypesThe basic strategy for selecting the best data type is to select the smallest data type that matches the kind of data you have and that allows for all the feasible values of your data. For example, You could let your database select data types for you when creating your table with sample records. However, this will rarely give 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 issues. The issues we encounter with the data types MySQL automatically selected are:
As smart as modern databases are, the owners of the data still know best about what kind of data is stored and what are the possible values the data can take in the foreseeable future. So you need to carefully specify the data types for each of your fields. Documentation on data types for different common databases are provided here:
ConclusionFor 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. What is category of data called?Data is a vast record of information segmented into various categories to acquire different types, quality, and characteristics of data, and these categories are called data types.
What data type should you assign to a field that stores postal codes?The right data type for a postal code is a string. In a SQL database, this would typically be VARCHAR() or CHAR() , of the appropriate length. A numeric data type is not appropriate even if the values look like a number. Here is one simple reason: you cannot perform arithmetic on them.
What are types of data quizlet?Quantitative Data. Numerical data that can be counted, ordered and measured.. Continuous. data that can take any value.. Discrete. data that is distinct and separate from each other, usually whole numbers.. Qualitative data. non-numerical data.. Ordinal. data that can be counted and ordered but not measured.. Nominal. ... . Mean. ... . Median.. What data type is used for sequential integers?The SERIAL data type stores a sequential integer, of the INT data type, that is automatically assigned by the database server when a new row is inserted.
|