A database data type refers to the format of data storage that can hold a distinct type or range of values. When computer programs store data in variables, each variable must be designated a distinct data type. Some common data types are as follows: integers, characters, strings, floating point numbers and arrays. More specific data types are as follows: varchar (variable character) formats, Boolean values, dates and timestamps.
There are programming languages that require the programmer to determine the data type of a variable before attaching a value to it. While some programming languages can automatically attach a data type to a variable based on the initial data assigned to the variable. For example, a variable is assigned with the value “3.75”, then the data type that will be attached to the variable is floating point.
Most of the programming languages enable each variable to store only a single data type. For example, if the data type attached to the variable is integer, when you assign a string data to the variable, the string data will be converted to an integer format.
Database applications use data types. Database fields require distinct type of data to be entered. For example, school record for a student may use a string data type for student’s first and last name. The student’s date of birth would be stored in a date format and the student’s GPA can be stored as decimal. By ensuring that the data types are consistent across multiple records, database applications can easily perform calculations, comparisons, searching and sorting of fields in different records.
Common Database Data Types
- Integer – is a whole number that can have a positive, negative or zero value. It cannot be a fraction nor can have decimal places. It is commonly used in programming especially for increasing values. Addition, subtraction and multiplication of two integers results to an integer. But division of two integers may result to an integer or a decimal. The resulting decimal can be rounded off or truncated to produce an integer.
- Character – refers to any number, letter, space or symbol that can be entered in a computer. Each character occupies one byte of space.
- String – is used to represent text. It is composed of a set of characters that can have spaces and numbers. Strings are enclosed in quotation marks to identify the data as string and not a variable name nor a number.
- Floating Point Number – is a number that contains decimals. Numbers that contain fractions are also considered as floating point numbers.
- Array – contains a group of elements which can be of the same data type like an integer or string. It is used to organise data for easier sorting and searching of related set of values.
- Varchar – as the name implies is variable character as the memory storage has variable length. Each character occupies one byte of space plus 2 bytes for length information.Note: Use Character for data entries with fixed length, like phone number. Use Varchar for data entries with variable length, like address.
- Boolean – is used for creating true or false statements. To compare values the following operators are being used: AND, OR, XOR, and NOT.
Boolean Operator Result Condition x AND y True If both x and y are True x AND y False If either x or y is False x OR y True If either x or y, or both x and y are True x OR y False If both x and y are False x XOR y True If only x or y is True x XOR y False If x and y are both True or both False NOT x True If x is False NOT x False If x is True
- Date, Time and Timestamp – these data types are used to work with data containing dates and times.