Designing the Products Table: A Practical Guide to SQL DDL

Previously in this course, we covered the fundamentals of creating your store database. Now that you have a database container to work in, it's time to populate it with structures that hold your data.

In the world of relational databases, DDL (Data Definition Language) is the set of SQL commands used to define or modify the structure of your database. The most important of these is CREATE TABLE. This command tells PostgreSQL exactly what information you want to store and what format that information must take.

The Anatomy of a CREATE TABLE Statement

Think of a table as a blueprint for a spreadsheet. Before you can add data, you must define the columns and the type of data each column will contain. This is the core of schema design.

A basic CREATE TABLE statement follows this structure:

SQL
CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type,
    ...
);

Choosing Data Types for Your Products

In our store application, we need to track products. A typical product needs a name, a description, a price, and an inventory count. Choosing the right data type for these attributes is a critical skill for any data engineer.

Note: As you explore defining entities and attributes in data modeling, you'll see why selecting precise types prevents data corruption and improves query performance.

Worked Example: Building the Products Table

Let's put this into practice. Open your psql terminal or your preferred GUI (like pgAdmin) connected to your store database and execute the following:

SQL
CREATE TABLE products (
    product_id SERIAL,
    name VARCHAR(255),
    description TEXT,
    price NUMERIC(10, 2),
    stock_quantity INTEGER
);

Once you execute this command, PostgreSQL creates the table structure. You can verify it exists by running \dt in your psql terminal.

Hands-on Exercise

Now it's your turn to expand your schema. We want to track when a product was added to our store.

1. Write a CREATE TABLE statement for a new table called categories that includes a category_id (use SERIAL) and a category_name (use VARCHAR(100)).
2. Execute the statement in your database.
3. Use the \d categories command to inspect your new table's definition.

Common Pitfalls to Avoid

• Using FLOAT for Currency: Never use REAL or FLOAT for money. They use binary floating-point arithmetic, which leads to rounding errors. Always use NUMERIC or DECIMAL for financial data.
• Over-allocating VARCHAR: While VARCHAR(255) is a common standard, don't set your limits to massive numbers (like VARCHAR(10000)) "just in case." Keep your schema tight.
• Ignoring Case Sensitivity: PostgreSQL defaults to lowercase for unquoted identifiers. Avoid using camelCase or mixed case for table and column names; stick to snake_case (e.g., product_id instead of ProductID). This makes your SQL much easier to read and maintain as you learn more about introduction to database design: schema basics for SaaS.

Frequently Asked Questions

Q: Can I change the table structure later if I forget a column? A: Yes. You can use the ALTER TABLE command to add, drop, or rename columns after the table is created.

Q: What happens if I don't specify a length for VARCHAR? A: In PostgreSQL, TEXT and VARCHAR (without a length) are effectively the same. However, using VARCHAR(n) is often used as a form of "data validation" to ensure the application doesn't accidentally send overly long strings.

Q: Why use SERIAL for IDs? A: SERIAL is a convenience feature that automatically creates a sequence and assigns the next integer to your column whenever a new row is inserted.

Recap

In this lesson, you learned that CREATE TABLE is your primary tool for defining your database structure. By selecting appropriate data types—like NUMERIC for prices and TEXT for descriptions—you ensure your database remains performant and accurate. You've now taken the first real step in building our store's inventory system.

Up next: Building the Customers Table — we will apply these same DDL principles to manage our user data.