Tables, Rows, Columns

Tables: The Fundamental Structure

A table is the basic structural unit in a relational database. It organizes data into a grid of rows and columns, similar to a spreadsheet. Each table represents a specific entity, such as customers, products, or orders.

Table Properties

1. Unique Name: Each table has a name that identifies it within the database.
2. Defined Schema: The structure (columns, data types, constraints) is defined upfront.
3. Atomic Values: Each cell contains a single, indivisible value.
4. No Duplicate Rows: Primary keys ensure uniqueness.

Creating a Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Modifying a Table

-- Add a new column
ALTER TABLE customers ADD COLUMN address VARCHAR(255);

-- Modify a column type
ALTER TABLE customers MODIFY COLUMN phone VARCHAR(25);

-- Drop a column
ALTER TABLE customers DROP COLUMN phone;

-- Rename a table
ALTER TABLE customers RENAME TO clients;

Table Naming Conventions

• Use lowercase and underscores (snake_case)
• Use plural nouns (customers, not customer)
• Avoid reserved SQL keywords
• Be descriptive but concise

-- Good table names
CREATE TABLE order_items (...);
CREATE TABLE user_profiles (...);

-- Bad table names
CREATE TABLE Order (...);  -- reserved keyword
CREATE TABLE t (...);      -- not descriptive

Tables are the containers that hold all your data. Understanding how to design and create them is the first step in database management.

Rows: Individual Records

A row (also called a record or tuple) represents a single instance of the entity described by the table. Each row contains values for each column defined in the table schema.

Characteristics of Rows

1. Each row is unique: Enforced by primary key constraints.
2. Order is not guaranteed: Rows have no inherent sequence (use ORDER BY for sorting).
3. Atomic values: Each cell contains one value.

Inserting Rows

-- Insert a single row
INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

-- Insert multiple rows
INSERT INTO customers (first_name, last_name, email)
VALUES 
    ('Jane', 'Smith', 'jane.smith@example.com'),
    ('Bob', 'Johnson', 'bob.j@example.com'),
    ('Alice', 'Williams', 'alice.w@example.com');

-- Insert with all columns (match column order)
INSERT INTO customers 
VALUES (DEFAULT, 'Charlie', 'Brown', 'charlie@example.com', NULL, CURRENT_TIMESTAMP);

Retrieving Rows

-- Select all rows
SELECT * FROM customers;

-- Select specific rows
SELECT first_name, last_name FROM customers WHERE customer_id = 1;

-- Count total rows
SELECT COUNT(*) AS total_customers FROM customers;

Updating Rows

-- Update a specific row
UPDATE customers 
SET email = 'john.new@example.com' 
WHERE customer_id = 1;

-- Update multiple columns
UPDATE customers 
SET first_name = 'Jonathan', last_name = 'Doe-Son' 
WHERE customer_id = 1;

Deleting Rows

-- Delete a specific row
DELETE FROM customers WHERE customer_id = 5;

-- Delete rows matching a condition
DELETE FROM customers WHERE created_at < '2023-01-01';

-- Delete all rows (table remains)
DELETE FROM customers;
-- or
TRUNCATE TABLE customers;  -- faster, resets auto-increment

Rows are the actual data entries in your table. Each row represents one real-world entity instance.

Columns: Attributes with Data Types

A column (also called an attribute or field) defines a specific property of the data stored in the table. Each column has a name, a data type, and optionally constraints.

Column Properties

1. Name: Identifies the attribute
2. Data Type: Determines what values can be stored
3. Constraints: Rules that validate data (NOT NULL, UNIQUE, etc.)

Common Data Types

Data Type	Description	Example
INT	Whole numbers	42
DECIMAL(p,s)	Exact decimal numbers	99.99
VARCHAR(n)	Variable-length string	'Hello'
TEXT	Large text data	Long paragraph
DATE	Calendar date	'2024-01-15'
TIMESTAMP	Date and time	'2024-01-15 10:30:00'
BOOLEAN	True/false value	TRUE
BINARY	Raw binary data	Byte array

Defining Columns with Constraints

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    category VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Column Selection in Queries

-- Select specific columns (better practice than *)
SELECT name, price, stock_quantity FROM products;

-- Aliases for columns
SELECT 
    name AS product_name,
    price AS unit_price,
    stock_quantity AS qty_available
FROM products;

-- Computed columns
SELECT 
    name,
    price,
    price * 0.1 AS tax_amount,
    price * 1.1 AS total_price
FROM products;

Column Naming Best Practices

• Use snake_case (product_id, not productID)
• Be descriptive (first_name, not fn)
• Avoid reserved keywords
• Use consistent naming across tables

Columns define what data your table can store. Choosing the right data types and constraints ensures data integrity and optimal performance.

Data Types in Detail

Choosing the correct data type is crucial for data integrity, storage efficiency, and query performance. Each database system has slightly different data type support.

Numeric Types

-- Integer types
SMALLINT    -- -32,768 to 32,767 (2 bytes)
INT         -- -2.1B to 2.1B (4 bytes)
BIGINT      -- ±9.2 × 10^18 (8 bytes)

-- Decimal types
DECIMAL(10,2)  -- exact, up to 10 digits, 2 after decimal
NUMERIC(8,3)   -- same as DECIMAL
FLOAT          -- approximate, 4 bytes
DOUBLE         -- approximate, 8 bytes

-- Example: Financial data should use DECIMAL, not FLOAT
CREATE TABLE transactions (
    id INT PRIMARY KEY,
    amount DECIMAL(15,2) NOT NULL,  -- exact precision
    rate FLOAT                       -- acceptable for approximations
);

String Types

-- Character types
CHAR(10)       -- fixed length, padded with spaces
VARCHAR(100)   -- variable length, up to 100 chars
TEXT           -- long text (up to 65,535 chars)

-- Example
CREATE TABLE articles (
    id INT PRIMARY KEY,
    code CHAR(3) NOT NULL,           -- fixed codes like 'USD'
    title VARCHAR(255) NOT NULL,
    summary VARCHAR(500),
    body TEXT                        -- full article content
);

Date and Time Types

DATE           -- '2024-01-15'
TIME           -- '14:30:00'
DATETIME       -- '2024-01-15 14:30:00'
TIMESTAMP      -- auto-updates, timezone-aware

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE NOT NULL,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Type Selection Guidelines

Scenario	Recommended Type
ID fields	INT or BIGINT
Money/financial	DECIMAL
Names/emails	VARCHAR
Long content	TEXT
Yes/no flags	BOOLEAN or TINYINT(1)
Dates only	DATE
Timestamps	TIMESTAMP
UUIDs	CHAR(36) or native UUID type

-- Best practice: Always consider storage and precision
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    age SMALLINT CHECK (age > 0 AND age < 150),
    is_verified BOOLEAN DEFAULT FALSE
);

Selecting appropriate data types prevents data loss, improves performance, and ensures your application can handle the data correctly.

Schema Definition

A schema is the blueprint of a database. It defines the structure including tables, columns, data types, relationships, and constraints. Think of it as the architectural plan for your data.

Schema Levels

1. Logical Schema: Tables, columns, data types, constraints
2. Physical Schema: Indexes, storage structure, partitions
3. External Schema: Views and access patterns for specific users

Creating a Complete Schema

-- Database creation
CREATE DATABASE store_db;
USE store_db;

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    country VARCHAR(50) DEFAULT 'USA',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    stock_quantity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
);

-- Orders table with foreign keys
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order items (junction table)
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Viewing Schema Information

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE customers;
-- or
SHOW COLUMNS FROM customers;

-- Show create table statement
SHOW CREATE TABLE orders;

Schema Design Principles

1. Normalization: Reduce data redundancy (1NF, 2NF, 3NF)
2. Consistent Naming: Use snake_case, singular nouns for tables
3. Primary Keys: Every table should have one
4. Foreign Keys: Enforce referential integrity
5. Appropriate Data Types: Match the data requirements

Schema Migration

-- Adding a column to existing schema
ALTER TABLE customers 
ADD COLUMN loyalty_points INT DEFAULT 0;

-- Creating an index for performance
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_customer ON orders(customer_id);

A well-designed schema is the foundation of a reliable, performant database. Take time to plan your schema before implementing.