What are some alternative structural solutions to the problem of storing multiple categories for a single entry in PHP databases?

Storing multiple categories for a single entry in PHP databases can be achieved by creating a separate table to store the categories and then linking them to the main entry through a many-to-many relationship using a junction table. 

// Create a categories table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

// Create a junction table to link entries and categories
CREATE TABLE entry_categories (
    entry_id INT,
    category_id INT,
    PRIMARY KEY (entry_id, category_id),
    FOREIGN KEY (entry_id) REFERENCES entries(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

// Insert a new entry and its categories
$entry_id = 1;
$categories = [2, 4]; // Assuming categories with ids 2 and 4 exist
foreach ($categories as $category_id) {
    $query = "INSERT INTO entry_categories (entry_id, category_id) VALUES ($entry_id, $category_id)";
    // Execute the query
}

Keywords

Normalization Many-to-many relationship Junction table Composite key Entity-attribute-value model

Related Questions