DDL - Designing Global Schemas

Schemas, Constraints & Professional Architecture

In a global retail empire, the data structure is the blueprint of the business. Before a single sale can be recorded, we must design the "Containers" for that data. This is Data Definition Language (DDL).

Professional DDL goes beyond just creating tables. It involves setting "Rules" (Constraints) that prevent bad data from ever entering the system. In this chapter, we will learn how to build robust, scalable architectures that ensure data integrity across continents.

Constraints: The Guardians of Integrity

A database is only useful if the data inside it is trustworthy. Constraints are rules enforced at the database level to ensure this.

  • PRIMARY KEY: Uniquely identifies every record (e.g., Transaction ID).
  • FOREIGN KEY: Connects tables together (e.g., linking a sale to a specific customer).
  • NOT NULL: Ensures a field is never left empty (e.g., Price).
  • CHECK: Ensures values fall within a logical range (e.g., Discount < 100%).

SQL
CREATE TABLE inventory (    item_id SERIAL PRIMARY KEY,    name TEXT NOT NULL,    stock_level INT CHECK (stock_level >= 0));

ALTER: Changing the Plane Mid-Flight

Businesses change. A global retailer might start accepting Bitcoin, requiring a new 'wallet_address' column, or might need to change a column's name for clarity. The ALTER command allows us to modify structures without deleting the millions of rows of data already inside them.

SQL
ALTER TABLE customers RENAME COLUMN phone TO contact_number;

DROP vs TRUNCATE: Choosing the Right Eraser

Knowing how to remove structures is as important as knowing how to build them.

  • TRUNCATE: Instantly wipes all data but keeps the "Empty Box" (structure) ready for new data. This is very fast.
  • DROP: Deletes the data AND the "Box" itself. It is permanent and irreversible.

Warning: Use these with the same caution as a surgeon uses a scalpel!

SQL
TRUNCATE TABLE logs; -- Keeps the logs table but empties it.DROP TABLE legacy_data; -- Legacy_data is gone forever.
SQLRuns entirely in your browser — nothing is sent to a server.

Practice Questions

Question 1

Which constraint prevents two rows from having identical IDs?

  • PRIMARY KEY
  • FOREIGN KEY
  • LIMIT
  • TRIAL

Question 2

To link an 'Order' to a 'Customer', which type of constraint would you use in the orders table?

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL

Question 3

Which command would you use to add a 'middle_name' column to your Users table?

  • UPDATE
  • CREATE
  • ALTER
  • INSERT

Question 4

If you want to quickly wipe all data from a table but keep it for future use, which is most efficient?

  • DELETE
  • DROP
  • TRUNCATE
  • RENAME

Question 5

What does the CHECK constraint do?

  • It checks if the user is logged in
  • It verifies that data matches a specific logical condition
  • It sends a notification to the manager
  • It verifies the spelling of words