Introduction to Data Types: Numeric vs. String

What is a Data Type?

Defining the Rules

In a database, a Data Type is a constraint that tells the system what kind of information can live in a column. It ensures that your data remains clean, organized, and efficient.

• Storage Efficiency: Saving space by using only what is necessary.
• Data Integrity: Preventing invalid entries (like letters in a price field).
• Performance: Speeding up searches and mathematical operations.

Welcome to the foundation of database design. Before we can store data, we must define the rules for it. A data type is like a container shape; it specifies exactly what fits inside. This ensures we don't waste storage space, keeps our data accurate by blocking invalid inputs, and allows the database to process numbers and text at lightning speeds.

• Data types define the kind of data a column can hold.
• They optimize storage and ensure data validity.
• Choosing the right type improves database performance.

Numeric vs. String Families

The Two Major Families

Most SQL data falls into two categories: Numeric and String.

Numeric Types

• Used for math (prices, counts, IDs).
• Highly efficient for sorting.

String Types

• Used for text (names, descriptions, codes).
• Can include letters, symbols, and leading zeros.

Almost all data you'll work with belongs to one of two families. On the left, we have Numeric types. Use these for anything you might need to add, subtract, or average—like prices or counts. On the right are String types. These are for descriptive text like names, or codes that might contain letters and symbols.

• Numeric types are for calculations and efficient sorting.
• String types handle text, symbols, and formatted codes.

Deep Dive: Numeric Constraints

Optimizing Numbers

When working with numbers, we can use modifiers like UNSIGNED to maximize our range.

• Integers: Whole numbers without decimals.
• UNSIGNED: Removes negative numbers to double the positive capacity.

For example, SMALLINT UNSIGNED fits values from 0 up to 65,535.

When defining numeric columns, we want to be precise. Normally, an integer splits its range between negative and positive values. However, if we know a value will never be negative—like a Movie ID—we use the UNSIGNED modifier. This shifts the entire range to the positive side, allowing a SMALLINT UNSIGNED to store up to 65,535 unique values perfectly.

• Integers are whole numbers.
• UNSIGNED doubles the positive range by excluding negative values.
• SMALLINT UNSIGNED is ideal for IDs up to ~65,000.

Deep Dive: CHAR vs. VARCHAR

Handling Text

Strings come in two main flavors depending on how they use space.

• VARCHAR(N): Variable-length. Uses only the space needed for the text.
• CHAR(N): Fixed-length. Always uses the full allocated space, padded with blanks.

Not all text is stored the same way. VARCHAR is like an elastic band; if you define it as 50 characters but only use 10, it only takes up 10 units of space. CHAR, however, is like a rigid box. A CHAR(4) always takes up 4 units, which is perfect for standardized codes like movie ratings.

• VARCHAR is flexible and saves space for varying text lengths.
• CHAR is consistent and efficient for fixed-length codes.
• Use CHAR for standardized data like 'PG13' or 'R'.

The Sorting Pitfall

Why Types Matter for Sorting

If you store numbers as a String, the database sorts them alphabetically, not numerically.

Try sorting the lists below to see the difference.

A common mistake is storing numbers as strings. Try clicking the 'Sort' button on both columns. Notice how the Numeric column sorts by value, but the String column puts '100' before '25' because it only looks at the first digit. This is why prices and counts must always be Numeric!

• Numeric types sort by value (1, 2, 10).
• String types sort character-by-character (1, 10, 2).

Building the Movie Table

Practical Application

Let's apply what we've learned to our Movies table design:

• MovieID: SMALLINT UNSIGNED (Fits 65k IDs).
• Title: VARCHAR(50) (Flexible length for names).
• Rating: CHAR(4) (Fixed length for codes like 'PG13').

Let's design our Cinema database. For MovieID, we expect 60,000 movies, so SMALLINT UNSIGNED is the perfect fit. Titles vary greatly, so VARCHAR(50) saves space for short names while allowing long ones. Finally, for Ratings like 'G' or 'PG13', we use CHAR(4) to keep our codes consistent.

• Select types based on specific data constraints.
• Use SMALLINT UNSIGNED for IDs up to 65,535.
• Use CHAR(4) for standardized ratings.

Data Type Consultant

You are a database consultant. A client needs to store US Zip Codes (e.g., '02134'). Which family should they use and why?

Let's test your judgment. A client wants to store Zip Codes. Think about the rules we discussed: do we do math on them? Do leading zeros matter? Type your recommendation and explain your reasoning.

• Zip codes are strings because leading zeros must be preserved.
• Zip codes are not used for mathematical calculations.