Designing Table Definitions

In this lesson, you will learn how to translate your data requirements into formal table structures using Data Definition Language (DDL).

Defining the Blueprint

Up until now, we have been working with data that already exists in tables. Today, we move to the other side of the desk: creating the tables themselves. To build a table, we use the CREATE TABLE statement. This command acts as a blueprint, telling the database exactly what to call the table and, more importantly, what kind of data each column can hold.

The Anatomy of CREATE TABLE

When you define a column, you must pair its name with a specific data type. This ensures data integrity—you wouldn't want someone saving "Hello" into a column meant for a price or a birth date!

Consider a scenario where we are tracking a library’s inventory. We need to store a book title (a string), the year it was published (a number), and its price (a decimal number). Here is how we define that:

CREATE TABLE Books (
    book_id INT,
    title VARCHAR(255),
    published_year INT,
    price DECIMAL(10, 2)
);

Understanding Precision

In the example above, notice the parameters:

• VARCHAR(255): This tells the database the column is a string that can hold up to 255 characters. Setting a limit helps the database manage memory efficiently.
• DECIMAL(10, 2): This is essential for financial data. The 10 represents the total number of digits allowed (the precision), and the 2 represents how many of those digits appear after the decimal point (the scale).

By choosing these types carefully, you ensure that your database remains performant and your data remains accurate. If you try to insert text into an INT column, the database will throw an error, protecting your table from "dirty" data.

You have now moved from being a consumer of data to a designer of data structures. By explicitly defining your columns and types, you create a reliable foundation for all the queries, joins, and aggregates you will write later.

Key Takeaway: Designing a table requires choosing precise data types and constraints to ensure that your database only stores valid, predictable information.