Different Data Types
What Are Data Types in Databases?
A data type defines the kind of value that can be stored in a database field.
Choosing the right data type ensures data consistency, efficient storage, and optimised performance.
Common Data Types in Relational Databases
Numeric Data Types
- INTEGER: Whole numbers (e.g. 1, 2, 100).
- DECIMAL: Fixed-point numbers with decimal places.
- FLOAT/REAL: Approximate decimal numbers used in scientific calculations.
| Price (DECIMAL) | Quantity (INTEGER) | Discount Rate (FLOAT) |
|---|---|---|
| 19.99 | 3 | 0.05 |
| 120.50 | 1 | 0.10 |
String (Character) Data Types
- CHAR(n): Fixed-length string (e.g. "YES", "NO").
- VARCHAR(n): Variable-length string (e.g. "John Smith").
- TEXT: Large blocks of text such as comments or descriptions.
| Active (CHAR) | Name (VARCHAR) | Notes (TEXT) |
|---|---|---|
| YES | Elena James | Returning customer, prefers email contact. |
| NO | Tom Nguyen | Requested callback for account setup. |
Date and Time Data Types
- DATE: Stores date values (e.g. "2025-03-15").
- TIME: Stores time values (e.g. "14:30:00").
- TIMESTAMP: Stores both date and time (e.g. "2025-03-15 14:30:00"). Sometimes
DATETIME
| Order Date (DATE) | Dispatch Time (TIME) | Recorded At (TIMESTAMP) |
|---|---|---|
| 2025-03-10 | 08:45:00 | 2025-03-10 08:45:00 |
| 2025-03-11 | 16:15:00 | 2025-03-11 16:15:00 |
Boolean Data Type
- BOOLEAN: Stores true/false values (e.g. 1 for True, 0 for False).
| Subscribed (BOOLEAN) | OptedInForEmails (BOOLEAN) |
|---|---|
| 1 | 0 |
| 0 | 0 |
Binary Data Types
- BLOB: Stores binary data such as images, audio, and video.
| FileName | FileType | FileData (BLOB) |
|---|---|---|
| invoice.pdf | application/pdf | [binary content] |
| logo.jpg | image/jpeg | [binary content] |
Special Data Types
- ENUM: Stores one predefined value from a list (e.g. "Small", "Medium", "Large").
| OrderID | Size (ENUM) |
|---|---|
| 301 | Medium |
| 302 | Large |
Potential Effects of Choosing the Wrong Data Type
- Increased Storage Space: Using TEXT instead of VARCHAR can waste space.
- Performance Issues: Storing numbers as VARCHAR slows calculations and indexing.
- Data Integrity Errors: Using incorrect types may allow invalid values (e.g. letters in a number field).
- Loss of Precision: Using FLOAT instead of DECIMAL may lead to rounding errors in financial data.
Why Is Data Type Consistency Important?
- Data Accuracy: Prevents incorrect or inconsistent data entry.
- Efficient Storage: Reduces unnecessary memory usage.
- Faster Queries: Improves database performance and indexing.
- Data Validation: Ensures only appropriate values are stored.
Key Takeaways
- Data types define how data is stored and interpreted by the database system.
- Choosing the right data type improves efficiency, accuracy, and storage.
- Relational databases use data types like INTEGER, VARCHAR, DATE, BOOLEAN, and BLOB.
- Incorrect types can lead to data quality issues, slower performance, and wasted space.