SQL Data Types | Designing Tables and Storage Optimization

# CHAPTER 12

Designing Tables and Choosing Data Types #

1. Introduction #

• TINYINT: Uses 1 byte. Stores numbers from 0 to 255. (Perfect for age or statuscodes).

• INT: Uses 4 bytes. Stores up to 2.1 Billion. (The absolute industry standard for id Primary Keys).

• BIGINT: Uses 8 bytes. Stores massive numbers. (Used for Primary Keys at Facebook/Google scale, or for tracking global website views).

5. String (Text) Data Types #

• CHAR(X): Fixed length. If you define CHAR(10) and insert "Hi", the database secretly adds 8 blank spaces to fill the void. (Use ONLY for data that is *always* the exact same length, like state_code 'NY' or a 32-character MD5 Hash).

• VARCHAR(X): Variable length. VARCHAR(255) means it can hold *up to* 255 characters, but if you insert "Hi", it only consumes 2 bytes! (The absolute industry standard for email, names, titles).

• TEXT: Used for massive blocks of text, like blog post contents or JSON payloads.

6. Financial Data Types (Crucial!) #

• DECIMAL(M, D): Mathematically exact. DECIMAL(10, 2) means the number can be up to 10 digits total, with exactly 2 digits after the decimal point (e.g., 99999999.99). This is mandatory for accounting systems.

7. Temporal Data Types (Time) #

• DATE: Stores Year, Month, Day (2024-10-31).

• DATETIME: Stores Date and Time (2024-10-31 14:30:00).

• TIMESTAMP: Similar to DATETIME, but automatically converts to UTC timezone for storage. (Highly recommended for logging when users click buttons globally).

8. Mini Project: Designing an Optimized User Table #

CREATE TABLE optimized_users (
    -- 4 Bytes. Up to 2 Billion users.
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Variable length, saves space for short names.
    username VARCHAR(50) NOT NULL,
    
    -- Exact 60-character length for bcrypt password hashes!
    password_hash CHAR(60) NOT NULL,
    
    -- 1 Byte. True (1) or False (0).
    is_admin TINYINT(1) DEFAULT 0,
    
    -- Automated logging using TIMESTAMP
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

9. Common Mistakes #

• The "VARCHAR(255) for Everything" Fallacy: Junior developers often default every single string column to VARCHAR(255). While this doesn't waste hard drive space, it drastically reduces RAM efficiency when the database engine tries to sort or index the data. Restrict your strings to logical limits (e.g., VARCHAR(50) for a name).

10. Best Practices #

• Use ENUM for Statuses: If an order_status can only ever be "Pending", "Shipped", or "Delivered", use the ENUM('Pending', 'Shipped', 'Delivered') data type. The database will physically block any other text from being inserted, protecting your application logic.

11. Exercises #

1. 1. What data type is the mandatory industry standard for storing monetary values like Prices or Bank Balances?

1. 2. What is the functional difference between CHAR(50) and VARCHAR(50)?

12. Database Design Challenges #

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Compare and contrast DATETIME and TIMESTAMP. In a globally distributed SaaS application where users exist across 10 different time zones, which data type should you use to record a "Purchase Event" and why?

• Q: Explain the RAM and storage performance implications of using CHAR(255) versus VARCHAR(255) when storing user First Names.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #