Columns and Data Types

In the previous section, we established that relational databases organize data into tables, which look much like spreadsheets with rows and columns. Now, let's focus on the columns. Think of columns as the vertical structures in a table. Each column represents a specific attribute or characteristic of the items being stored in the table. For example, in a table storing information about customers, you might have columns for CustomerID, FirstName, LastName, and EmailAddress.

Every column in a database table must be assigned a specific data type. This is a fundamental rule that tells the database exactly what kind of data is allowed in that column for every row. Why is this so important?

1. Data Integrity: Data types act as a validation rule. If you define a column to hold only numbers, the database will prevent you from accidentally inserting text like "Hello" into it. This helps ensure the accuracy and consistency of your data.
2. Storage Efficiency: Different kinds of data require different amounts of storage space. An integer typically takes less space than a long paragraph of text. By specifying the correct data type, the database can manage storage more effectively.
3. Performance: When the database knows the type of data in a column, it can perform operations like searching, sorting, or calculations much faster. Searching for a specific date is more efficient in a column defined as a DATE type than in a general text column where dates might be stored in various formats.

Common Data Types

While the exact names and variations can differ slightly between different Database Management Systems (DBMS), most relational databases support a standard set of data types. Here are some of the most common ones you'll encounter:

Numeric Types

These are used for storing numbers.

• Integer Types: Used for whole numbers (numbers without fractions). Examples include a customer ID (101), the quantity of an item ordered (5), or someone's age (30). Common integer types include:
  • INT (or INTEGER): A standard integer, suitable for many general-purpose uses.
  • SMALLINT: Uses less storage than INT but has a smaller range of possible values. Good for numbers you know won't be very large.
  • BIGINT: Uses more storage but can hold extremely large whole numbers.
• Fixed-Point Types (DECIMAL or NUMERIC): Used for numbers where exact precision is important, especially for calculations involving money. You typically specify the total number of digits and the number of digits after the decimal point. Example: Storing a price like $19.99. Using these types avoids the small rounding errors that can occur with floating-point types.
• Floating-Point Types (FLOAT or REAL): Used for approximate-number data values, often for scientific calculations where absolute precision down to the last decimal place might not be required or possible. They can represent a very large range of values but might introduce tiny rounding differences.

String (Text) Types

These are used for storing text data.

• VARCHAR(n): Stores variable-length character strings, where n is the maximum number of characters allowed. If you store text shorter than n, it only uses the space needed for the actual text plus a small amount of overhead. This is very common for names, email addresses, or short descriptions. For example, VARCHAR(50) could store a first name.
• CHAR(n): Stores fixed-length character strings, where n is the exact number of characters. If you store text shorter than n, the database often adds spaces (padding) to fill it up to the length n. This can be useful for data that always has the same length, like a two-letter state code (e.g., CHAR(2) for 'CA', 'NY', 'TX'). If storage isn't a major concern, VARCHAR is often more flexible.
• TEXT (or similar types like CLOB): Used for storing long blocks of text, like product descriptions, articles, or user comments, often exceeding the typical limits of VARCHAR.

Date and Time Types

These are specifically designed for storing calendar dates and/or clock times.

• DATE: Stores a date (year, month, and day). Example: 2024-07-15.
• TIME: Stores a time (hour, minute, and second). Example: 14:30:00.
• TIMESTAMP or DATETIME: Stores both date and time information together. Example: 2024-07-15 14:30:00. Useful for logging exactly when an event occurred.

Boolean Type

Used for storing true or false values. Different databases might call this BOOLEAN, BOOL, or sometimes use a BIT type or even a tiny integer like TINYINT(1) where 0 represents false and 1 represents true. Example: IsActive, HasShipped.

Choosing the Right Data Type

Selecting the most appropriate data type for each column is an important part of designing a database table. You should choose the type that best fits the data you intend to store.

• Don't use a text type for storing numbers if you plan to perform mathematical calculations on them.
• Don't use a floating-point type for currency if precise calculations are needed; use DECIMAL or NUMERIC.
• Choose VARCHAR over TEXT if you know the maximum length of the text will be reasonably short, as it can be more efficient.
• Use specific DATE or TIMESTAMP types for dates and times; storing them as text makes comparisons and calculations difficult. For instance, trying to find all orders before a certain date is simple with a DATE type but complex if dates are stored as strings like "July 15, 2024".
• Consider if a value is required or if it can be missing.

NULL: Representing Missing Information

Sometimes, a value for a particular column in a specific row might be unknown or not applicable. Databases use a special marker called NULL to represent this missing information. It's important to understand that NULL is not the same as zero (0) for numbers, nor is it the same as an empty string ('') for text. It specifically means "no value present". When defining a column, you can usually specify whether it is allowed to contain NULL values or if it must always have a value.

Example: A Simple Products Table Structure

Let's visualize this with a simple structure for a table storing product information:

ColumnName	DataType	Description	Allows NULL?
ProductID	INT	Unique identifier for the product (likely PK)	No
ProductName	VARCHAR(100)	Name of the product	No
Description	TEXT	Detailed description of the product	Yes
Price	DECIMAL(10,2)	Selling price (e.g., up to 99,999,999.99)	No
QuantityOnHand	INT	Number of units currently in stock	No
DateAdded	DATE	Date the product was added to the catalog	Yes
IsDiscontinued	BOOLEAN	True if the product is no longer sold	No

Example definition for a Products table showing column names, their assigned data types, a brief description, and whether NULL values are permitted.

In this example, each column has a carefully chosen data type. ProductID and QuantityOnHand are whole numbers (INT). ProductName uses VARCHAR(100) assuming names won't exceed 100 characters. Price uses DECIMAL(10, 2) for accurate currency storage. Description uses TEXT for potentially long descriptions. DateAdded uses DATE, and IsDiscontinued uses BOOLEAN. Notice also how some columns (ProductID, ProductName, etc.) are defined to not allow NULL values, ensuring certain essential information is always present for each product record.

Understanding columns and data types is essential for creating well-structured and reliable relational databases. They enforce consistency and enable the database to store and retrieve information efficiently. As you progress, you'll see how these fundamental building blocks support more complex database operations and designs.