Tables and Schemas Guide

Introduction

Tables are the fundamental structure in MySQL databases where data is stored. This guide covers creating, modifying, and managing tables effectively.

What is a Table?

A table is a collection of related data organized in rows (records) and columns (fields). Each column has a specific data type and constraints.

Table Structure Components

1. Columns (Fields)

• Define the type of data stored
• Have specific data types
• Can have constraints

2. Rows (Records)

• Individual entries in the table
• Contain actual data values

3. Primary Key

• Unique identifier for each row
• Cannot be NULL
• Should be immutable

4. Foreign Keys

• References primary key in another table
• Maintains referential integrity

Creating Tables

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

Simple Example

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Complete Example with Multiple Constraints

CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Common Constraints

1. NOT NULL

Ensures column cannot have NULL value:

username VARCHAR(50) NOT NULL

2. UNIQUE

Ensures all values in column are different:

email VARCHAR(100) UNIQUE

3. PRIMARY KEY

Uniquely identifies each record:

id INT PRIMARY KEY AUTO_INCREMENT

4. FOREIGN KEY

Links two tables together:

FOREIGN KEY (user_id) REFERENCES users(id)

5. DEFAULT

Sets default value if none specified:

status VARCHAR(20) DEFAULT 'active'

6. CHECK

Ensures values meet specific condition:

age INT CHECK (age >= 18)

7. AUTO_INCREMENT

Automatically generates unique numbers:

id INT AUTO_INCREMENT

Creating Tables in phpMyAdmin

1. Select Database:
   • Click on database in left sidebar
2. Create New Table:
   • Click “New” or enter table name
   • Specify number of columns
   • Click “Go”
3. Define Columns:
   • Name: Column name
   • Type: Data type (INT, VARCHAR, etc.)
   • Length/Values: Size specification
   • Default: Default value
   • Collation: Character set collation
   • Attributes: UNSIGNED, BINARY, etc.
   • Null: Allow NULL values
   • Index: PRIMARY, UNIQUE, INDEX
   • A_I: AUTO_INCREMENT
4. Save Table:
   • Click “Save”

Viewing Table Structure

Show All Tables

SHOW TABLES;

Describe Table Structure

DESCRIBE table_name;
-- or
DESC table_name;
-- or
SHOW COLUMNS FROM table_name;

Show CREATE TABLE Statement

SHOW CREATE TABLE table_name;

Modifying Tables

Add Column

ALTER TABLE table_name
ADD column_name datatype constraints;

Example:

ALTER TABLE users
ADD phone VARCHAR(20);

Modify Column

ALTER TABLE table_name
MODIFY column_name new_datatype new_constraints;

Example:

ALTER TABLE users
MODIFY email VARCHAR(150) NOT NULL;

Change Column Name and Definition

ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype constraints;

Example:

ALTER TABLE users
CHANGE username user_name VARCHAR(60) NOT NULL;

Drop Column

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE users
DROP COLUMN phone;

Rename Table

RENAME TABLE old_table_name TO new_table_name;
-- or
ALTER TABLE old_table_name RENAME TO new_table_name;

Working with Indexes

Create Index

CREATE INDEX index_name ON table_name(column_name);

Create Unique Index

CREATE UNIQUE INDEX index_name ON table_name(column_name);

Drop Index

DROP INDEX index_name ON table_name;
-- or
ALTER TABLE table_name DROP INDEX index_name;

Show Indexes

SHOW INDEX FROM table_name;

Table Relationships

One-to-One Relationship

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

One-to-Many Relationship

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Many-to-Many Relationship

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Copying Tables

Copy Structure Only

CREATE TABLE new_table LIKE existing_table;

Copy Structure and Data

CREATE TABLE new_table AS SELECT * FROM existing_table;

Copy Specific Columns

CREATE TABLE new_table AS
SELECT column1, column2 FROM existing_table;

Deleting Tables

Drop Table

DROP TABLE table_name;

Drop Multiple Tables

DROP TABLE table1, table2, table3;

Drop with Safety Check

DROP TABLE IF EXISTS table_name;

Truncate Table (Delete All Data, Keep Structure)

TRUNCATE TABLE table_name;

Storage Engines

InnoDB (Recommended)

• Supports foreign keys
• Transaction support
• Row-level locking
• Better for write-heavy operations

CREATE TABLE table_name (
    -- columns
) ENGINE=InnoDB;

MyISAM

• No foreign key support
• Table-level locking
• Faster for read-heavy operations
• Legacy engine

CREATE TABLE table_name (
    -- columns
) ENGINE=MyISAM;

Show Table Engine

SHOW TABLE STATUS WHERE Name = 'table_name';

Change Table Engine

ALTER TABLE table_name ENGINE = InnoDB;

Best Practices

1. Naming Conventions

• Use lowercase with underscores
• Plural names for tables (users, products)
• Singular names for columns
• Descriptive names

2. Primary Keys

• Always include a primary key
• Use AUTO_INCREMENT for simple IDs
• Consider UUID for distributed systems

3. Data Types

• Choose appropriate data types
• Use smallest sufficient size
• Be consistent across tables

4. Indexes

• Index columns used in WHERE clauses
• Index foreign keys
• Don’t over-index (impacts INSERT/UPDATE performance)

5. Foreign Keys

• Use foreign keys to maintain referential integrity
• Specify ON DELETE and ON UPDATE actions

6. Normalization

• Avoid data redundancy
• Follow normal forms (1NF, 2NF, 3NF)
• Balance normalization with performance

Example: Complete Blog Schema

-- Categories table
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Users table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('admin', 'author', 'subscriber') DEFAULT 'subscriber',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Posts table
CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    content TEXT NOT NULL,
    excerpt VARCHAR(500),
    author_id INT NOT NULL,
    category_id INT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    published_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_published_at (published_at)
) ENGINE=InnoDB;

-- Comments table
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT,
    author_name VARCHAR(100),
    author_email VARCHAR(100),
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'spam') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_post_id (post_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

Practice Exercises

Exercise 1: Create a Simple Table

Create a table for storing book information with appropriate data types and constraints.

Exercise 2: Modify Table

Add a new column to an existing table and create an index on it.

Exercise 3: Table Relationships

Create a set of related tables for a simple e-commerce system (customers, orders, order_items).

Next Steps

• Data Types
• SQL Queries
• Database Design Best Practices

Additional Resources

• MySQL CREATE TABLE Documentation
• MySQL Data Types
• MySQL Constraints