Deep Dive: Strings and Integers

Choosing the Right Containers

The Storage Strategy

In database design, selecting the correct data type is like choosing the right container for a shipment. If the container is too small, the data won't fit; if it's too large, you waste expensive space.

We will focus on two core categories: Integers and Strings, using our Movie database as a guide.

Welcome to our deep dive into SQL data types. Think of these types as containers. Choosing the right size ensures your data fits perfectly without wasting expensive server resources. Today, we'll build the foundation of our Movie database by mastering integers and strings.

Data types optimize storage and performance.
Integers handle whole numbers.
Strings handle text data.

The Right Container for Data

We will explore how to optimize our database using integers and strings, specifically for our Movie database.

Welcome! Selecting a data type is much like choosing a shipping container. Pick one too small, and your data overflows. Pick one too large, and you're paying for empty space. Today, we'll master the art of finding the perfect fit for whole numbers and text.

Data types determine storage efficiency.
Choosing the wrong size leads to errors or wasted space.
Focus on Integers and Strings.

Mastering Integer Sizes

Integers store whole numbers. To optimize your database, you must choose a size that fits your maximum expected value.

TINYINT: 1 byte (-128 to 127)
SMALLINT: 2 bytes (-32,768 to 32,767)
INT: 4 bytes (~ -2.1B to 2.1B)

Integers come in different sizes, measured in bytes. TINYINT is perfect for small values like Age. SMALLINT handles larger counts up to thirty-two thousand. And standard INT handles billions. Choosing the smallest one that fits is the key to a lean database.

TINYINT is for very small counts.
SMALLINT handles thousands of records.
INT is the standard for millions or billions.

Mastering Integer Sizes

Integer Hierarchy

Whole numbers come in different sizes. Selecting the smallest one that fits your data is key to optimization.

TINYINT: 1 byte (-128 to 127)
SMALLINT: 2 bytes (-32,768 to 32,767)
INT: 4 bytes (~2.1 billion)

Integers store whole numbers, but not all integers are created equal. TINYINT uses just one byte, perfect for something like a person's age. SMALLINT gives you more room, up to about 32,000. And INT is the heavy hitter, handling billions of rows. Always pick the smallest size that meets your maximum expected value.

TINYINT is for very small counts (like age).
SMALLINT is for medium ranges.
INT is the standard for large datasets.

The Power of UNSIGNED

Expanding the Range

By default, integers are SIGNED (allowing negative numbers). However, many columns like IDs or counts are never negative.

Applying the UNSIGNED attribute removes negative values and doubles the positive range.

Most database columns, like our Movie IDs, will never be negative. By applying the UNSIGNED constraint, we shift the entire range to the right. Notice how the maximum value for a SMALLINT jumps from 32,767 to over 65,000. This makes it the perfect, space-efficient choice for our Movie table.

UNSIGNED shifts the range to start at 0.
It doubles the maximum possible positive value.
Ideal for IDs, primary keys, and quantities.

The UNSIGNED Advantage

By default, integers are SIGNED (positive and negative). By applying the UNSIGNED attribute, you remove negative numbers and double the positive range.

This is ideal for Primary Keys like movie_id which are never negative.

Most integers are SIGNED, meaning the range is split between negative and positive. But when we apply the UNSIGNED constraint, we shift that entire capacity to the positive side, effectively doubling our limit without using more memory!

UNSIGNED removes negative numbers.
Doubles the maximum positive value.
Saves space by allowing smaller types to hold larger positive values.

Scenario: Sizing the Movie ID

Our Movie database will have a maximum of 65,000 movies. We need a space-efficient type for movie_id.

Which type fits perfectly?

Let's apply this. We expect up to 65,000 movies. A standard SMALLINT only goes up to 32,767. Click the option that would allow us to fit all 65,000 movies using only 2 bytes of space. Not quite. That might be too small or unnecessarily large. Remember, we need to reach 65,000 using the most efficient 2-byte type. Correct! By using SMALLINT UNSIGNED, we extend the range to 65,535, which fits our 65,000 movies perfectly while keeping the database small.

Evaluate max value vs. type capacity.
Apply the UNSIGNED constraint to maximize range.

Strings: CHAR vs. VARCHAR

Fixed vs. Variable Length

How you store text depends on whether the length is consistent or varying.

CHAR(N): Fixed-length. Always uses N characters. Padded with spaces if shorter.
VARCHAR(N): Variable-length. Uses only the actual characters + 1-2 bytes for length metadata.

When it comes to strings, think about storage efficiency. CHAR is fixed-length. If you define CHAR(4) for a rating like 'G', the database adds three invisible spaces to fill the block. VARCHAR, on the other hand, is flexible. For a movie title, it shrinks or grows to fit the actual text, saving precious space for shorter titles.

Use CHAR for standardized codes (e.g., 'US', 'PG13').
Use VARCHAR for names, titles, and descriptions.
VARCHAR saves space for varying data.

Strings: CHAR vs. VARCHAR

Text data requires different handling. SQL provides two main types:

CHAR(N): Fixed-length. Faster performance for consistent data.
VARCHAR(N): Variable-length. Saves space for inconsistent data.

Now for text. Think of CHAR as a fixed-size parking spot. If you park a small bike in a CHAR(4) spot, it still takes up all 4 spaces. VARCHAR is like an expandable bag; it grows or shrinks to fit the content, saving precious storage space.

CHAR pads short data with spaces.
VARCHAR uses only what is needed plus 1-2 bytes for length.
Use CHAR for codes; VARCHAR for titles and names.

The Movie Table Schema

Let's define the core columns for our Movie table based on what we've learned.

Match the Column Requirement to the Data Type.

It's time to build. Drag the correct data types to the table columns based on their specific needs. Remember, ratings like 'PG13' are always short and standardized. Excellent! You've optimized the Movie table. Using SMALLINT UNSIGNED for IDs, VARCHAR for titles, and CHAR for ratings ensures a high-performance database.

movie_id: SMALLINT UNSIGNED
title: VARCHAR(50)
rating: CHAR(4)

String Selection Practice

Drag the data labels to the correct SQL Data Type category based on their length characteristics.

Let's practice. Drag these data examples into the correct string category. Remember: if the length is always the same, choose CHAR. If it varies, go with VARCHAR. Exactly! That fits the storage pattern perfectly. Not quite. Think about whether that specific piece of data always has the same number of characters.

Identify fixed vs. variable patterns.
Apply CHAR for codes.
Apply VARCHAR for descriptive text.

The Movie Table Blueprint

Practical Application

Let's combine our knowledge to define the columns for our Movie table schema.

Column	Type
movie_id	SMALLINT UNSIGNED
title	VARCHAR(50)
rating	CHAR(4)

Here is our finalized blueprint for the Movie table. We use SMALLINT UNSIGNED for the ID to support up to 65,000 movies efficiently. The title is VARCHAR(50) because 'The Matrix' is much shorter than 'The Lord of the Rings'. Finally, ratings like 'PG13' are predictable, so CHAR(4) is our best bet.

movie_id: Optimized for 65k entries.
title: Flexible for short and long names.
rating: Standardized 4-character codes.

Design Challenge: The Pitfall Check

Optimization Diagnosis

A developer has proposed using INT for every numeric column and VARCHAR(255) for every text column. Why is this a problem?

Take a look at this 'lazy' schema design. Type your diagnosis in the box. Why should we avoid using generic large types for everything?

Avoid over-allocation.
Optimize for memory and sorting speed.
Use specific types to enforce data integrity.

Summary: Common Pitfalls

Avoid these common mistakes to keep your database professional and efficient:

Using INT for everything: Wastes space.
Over-allocating VARCHAR: Can slow down sorting operations.
Forgetting UNSIGNED: Limits your range unnecessarily for positive-only data.

Before we move on, remember: don't just use INT for everything. Be precise with your sizes. Don't over-allocate VARCHAR, and always use UNSIGNED for IDs and counts. You're now ready to start writing actual table creation code!

Be specific with integer sizes.
Don't default to VARCHAR(255).
Always consider if data can be negative.