What are the potential pitfalls of using separate tables for main categories and subcategories in PHP?

When using separate tables for main categories and subcategories in PHP, one potential pitfall is the need for multiple database queries to retrieve and display the hierarchical data. This can lead to slower performance and increased complexity in the code. To solve this issue, consider using a single table with a parent-child relationship to store both main categories and subcategories. 

// Table structure for categories
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

// Query to retrieve main categories and their subcategories
$query = "SELECT c1.name as main_category, c2.name as subcategory
          FROM categories c1
          LEFT JOIN categories c2 ON c1.id = c2.parent_id
          WHERE c1.parent_id IS NULL";

// Execute the query and display the results
$result = mysqli_query($connection, $query);
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['main_category'] . ' - ' . $row['subcategory'] . '<br>';
}

Keywords

separate tables main categories subcategories PHP database normalization

Related Questions