MySQL Table Creation

2. MySQL Data Types Reference

-- INT examples
age INT NOT NULL,
quantity INT DEFAULT 0,
user_id INT PRIMARY KEY AUTO_INCREMENT

-- Common patterns for web applications
id INT AUTO_INCREMENT PRIMARY KEY,           -- Primary key
name VARCHAR(255) NOT NULL,                  -- Product/User name
email VARCHAR(100) UNIQUE NOT NULL,          -- Email address
price DECIMAL(10,2) CHECK (price >= 0),      -- Monetary values
description TEXT,                            -- Long text
is_active BOOLEAN DEFAULT TRUE,              -- Status flags
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Audit trail
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- Auto-update

3. Table Relationships & Foreign Keys

One-to-Many Relationship Example

-- Categories table (one)
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table (many)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) 
        REFERENCES categories(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
        
    INDEX idx_category (category_id)
);

Many-to-Many Relationship Example

-- Products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Junction table for many-to-many
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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(id)
        ON DELETE CASCADE,
        
    FOREIGN KEY (product_id) 
        REFERENCES products(id)
        ON DELETE RESTRICT,
        
    UNIQUE KEY unique_order_product (order_id, product_id)
);

4. Table Creation with Next.js

Database Configuration

// lib/db.js
import mysql from 'mysql2/promise';

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'my_nextjs_app',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

const pool = mysql.createPool(dbConfig);

export async function query(sql, params) {
  const [rows] = await pool.execute(sql, params);
  return rows;
}

export { pool };

API Route for Table Creation

// pages/api/admin/create-table.js
import { query } from '../../../lib/db';

export default async function handler(req, res) {
  // Add proper authentication/authorization
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }

  const { tableName, schema } = req.body;

  try {
    // Basic validation
    if (!tableName || !schema) {
      return res.status(400).json({ 
        message: 'Table name and schema are required' 
      });
    }

    // Create table SQL
    const createTableSQL = `CREATE TABLE IF NOT EXISTS ${tableName} (${schema})`;

    await query(createTableSQL);

    res.status(200).json({ 
      message: `Table ${tableName} created successfully`,
      sql: createTableSQL
    });

  } catch (error) {
    console.error('Table creation error:', error);
    res.status(500).json({ 
      message: 'Error creating table', 
      error: error.message 
    });
  }
}

Frontend Component for Table Management

// components/TableCreator.js
import { useState } from 'react';

const predefinedSchemas = {
  users: `id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`,

  products: `id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`
};

export default function TableCreator() {
  const [tableName, setTableName] = useState('');
  const [schema, setSchema] = useState('');
  const [message, setMessage] = useState('');
  const [loading, setLoading] = useState(false);

  const handlePredefinedSchema = (type) => {
    setSchema(predefinedSchemas[type]);
    setTableName(type);
  };

  const handleSubmit = async (e) => {
    e.preventDefault();
    setLoading(true);
    setMessage('');

    try {
      const response = await fetch('/api/admin/create-table', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ tableName, schema }),
      });

      const data = await response.json();
      
      if (response.ok) {
        setMessage(`✅ ${data.message}`);
      } else {
        setMessage(`❌ ${data.message}`);
      }
    } catch (error) {
      setMessage(`❌ Error: ${error.message}`);
    } finally {
      setLoading(false);
    }
  };

  return (
    <div className="card">
      <div className="card-body">
        <h5 className="card-title">Create New Table</h5>
        
        <div className="mb-3">
          <label className="form-label">Quick Templates:</label>
          <div className="btn-group">
            <button 
              type="button" 
              className="btn btn-outline-primary btn-sm"
              onClick={() => handlePredefinedSchema('users')}
            >
              Users Table
            </button>
            <button 
              type="button" 
              className="btn btn-outline-primary btn-sm"
              onClick={() => handlePredefinedSchema('products')}
            >
              Products Table
            </button>
          </div>
        </div>

        <form onSubmit={handleSubmit}>
          <div className="mb-3">
            <label className="form-label">Table Name</label>
            <input
              type="text"
              className="form-control"
              value={tableName}
              onChange={(e) => setTableName(e.target.value)}
              placeholder="users"
              required
            />
          </div>
          
          <div className="mb-3">
            <label className="form-label">Table Schema</label>
            <textarea
              className="form-control"
              rows="8"
              value={schema}
              onChange={(e) => setSchema(e.target.value)}
              placeholder="id INT AUTO_INCREMENT PRIMARY KEY, ..."
              required
            />
          </div>
          
          <button 
            type="submit" 
            className="btn btn-primary"
            disabled={loading}
          >
            {loading ? 'Creating Table...' : 'Create Table'}
          </button>
        </form>

        {message && (
          <div className="mt-3 alert alert-info">{message}</div>
        )}
      </div>
    </div>
  );
}