What are the best practices for designing database tables in PHP to optimize data storage and retrieval?

When designing database tables in PHP to optimize data storage and retrieval, it is important to normalize your database structure by breaking down data into smaller, related tables to avoid redundancy and improve query performance. Additionally, use appropriate data types for columns to minimize storage space and ensure data integrity. Indexing columns that are frequently used in queries can also significantly improve retrieval speed.

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

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_user_id ON posts(user_id);