Chapter 2: Schema Design, Data Types & Table Creation

Welcome to Chapter 2. In software engineering, your application’s speed, data consistency, and long-term maintainability depend heavily on good Schema Design. A poorly chosen data type or missing foreign key index can lead to data corruption, orphaned records, and catastrophic performance slowdowns as your data grows.

In this chapter, we will examine the core MySQL data types, understand the role of relational constraints, and write production-grade Data Definition Language (DDL) statements to construct our corporate departments and employees tables.

🧩 2.1 Essential MySQL Data Types

Selecting the smallest data type that can safely accommodate all possible future values minimizes disk storage, accelerates index lookups, and maximizes RAM caching in the MySQL Buffer Pool.

🛡️ 2.2 Table Constraints & Data Integrity

Constraints are rules enforced by the database engine on table columns to prevent invalid data entry:

• PRIMARY KEY: Uniquely identifies each row. Automatically creates an underlying unique clustered B-Tree index and rejects NULL values.
• FOREIGN KEY: Ensures referential integrity by establishing that a value in the child table must already exist in the referenced parent table.
• NOT NULL: Mandates that the column must contain a valid value when a new row is inserted.
• UNIQUE: Guarantees that all values in that column are distinct from one another.
• DEFAULT: Provides a fallback value if none is specified during an insert.

💾 2.3 Designing the Corporate Schema (ERD)

We model an organizational structure containing Departments and Employees. An employee belongs to one department, while a department can employ multiple employees (a 1-to-Many relationship):

+----------------------+             +-----------------------+
|     DEPARTMENTS      |             |       EMPLOYEES       |
+----------------------+             +-----------------------+
| * department_id (PK) |<----+       | * employee_id (PK)    |
|   department_name    |     |       |   first_name          |
|   location_id        |     |       |   last_name           |
+----------------------+     |       |   email               |
                             |       |   phone_number        |
                             |       |   hire_date           |
                             |       |   job_id              |
                             |       |   salary              |
                             |       |   commission_pct      |
                             |       |   manager_id (Self-FK)|
                             +-------|   department_id (FK)  |
                                     +-----------------------+

✅ 2.4 Creating the Parent Table: departments

Because child tables reference parent tables, the parent table must be created first:

-- Create parent table: departments
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(30) NOT NULL,
    location_id INTEGER
);

👥 2.5 Creating the Child Table: employees

The employees table defines two crucial constraints: a Foreign Key pointing to departments(department_id), and a self-referencing relationship where manager_id refers to another employee’s employee_id:

-- Create child table: employees
CREATE TABLE employees (
    employee_id INTEGER,
    first_name VARCHAR(20),
    last_name VARCHAR(25) NOT NULL,
    email VARCHAR(25) NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary INTEGER NOT NULL,
    commission_pct INTEGER,
    manager_id INTEGER,
    department_id INTEGER,
    CONSTRAINT pk_emp PRIMARY KEY (employee_id),
    CONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

🔍 2.6 Verifying Schema Structure

After creating tables, use the following commands to inspect the schema metadata:

-- Inspect columns, types, nullability, and keys
DESCRIBE employees;

-- View the exact SQL definition including engines and constraints
SHOW CREATE TABLE employees\G

⚠ 2.7 Production Tips & Common Mistakes

• Always Index Foreign Keys: While MySQL automatically creates an index for foreign keys, ensure that any column frequently used in JOIN ... ON conditions is indexed to avoid full table scans.
• Avoid Floating Point for Money: Never use FLOAT or DOUBLE to store monetary values like salaries or prices. Floating-point numbers use binary approximations that cause rounding errors (e.g. 0.1 + 0.2 = 0.30000000000000004). Always use DECIMAL(10,2).

❓ Frequently Asked Questions (FAQ)

1. What happens if I try to delete a department that still has employees?

Because the foreign key constraint was defined without ON DELETE CASCADE, MySQL uses the default RESTRICT action and will throw Error 1451: Cannot delete or update a parent row: a foreign key constraint fails. This protects your database from orphaned employee records.

2. Can a Primary Key contain NULL values?

No. By SQL standard definition, a Primary Key must uniquely identify each row and cannot contain NULL.