如何实现多分类关联？三步过滤系统数据库优化问询

Got it, let's fix this redundancy issue properly. Your current approach of duplicating partner entries works but creates unnecessary maintenance headaches. Instead, we can use a junction table (association table) to link a single partner to multiple category paths—no duplicate partner data required. Here's how to implement it step by step:

1. 重构数据库结构 #

Step 1: Clean up the partner table #

First, we'll remove the category-related and redundancy fields from the partner table (since we're moving that relationship to a dedicated table):

-- Drop existing foreign keys first (required to remove linked columns)
ALTER TABLE `partner`
DROP FOREIGN KEY `partner_ibfk_1`,
DROP FOREIGN KEY `partner_ibfk_2`,
DROP FOREIGN KEY `partner_ibfk_3`;

-- Remove redundant columns
ALTER TABLE `partner`
DROP COLUMN `category_id`,
DROP COLUMN `subcategory_id`,
DROP COLUMN `subcategory2_id`,
DROP COLUMN `redundancy`;

Step 2: Create the junction table #

This table will store all links between partners and their category paths. Each row represents one category/subcategory/subcategory2 combination tied to a partner:

CREATE TABLE `partner_category_links` (
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `subcategory_id` int(11) NOT NULL,
  `subcategory2_id` int(11) NOT NULL,
  PRIMARY KEY (`link_id`),
  -- Add indexes for fast queries
  KEY `partner_id` (`partner_id`),
  KEY `category_id` (`category_id`),
  KEY `subcategory_id` (`subcategory_id`),
  KEY `subcategory2_id` (`subcategory2_id`),
  -- Foreign key constraints to enforce data integrity
  CONSTRAINT `partner_category_links_ibfk_1` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`partner_id`) ON DELETE CASCADE,
  CONSTRAINT `partner_category_links_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),
  CONSTRAINT `partner_category_links_ibfk_3` FOREIGN KEY (`subcategory_id`) REFERENCES `subcategories` (`subcategory_id`),
  CONSTRAINT `partner_category_links_ibfk_4` FOREIGN KEY (`subcategory2_id`) REFERENCES `subcategories2` (`subcategory2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 3: Migrate existing data #

Move your existing category relationships to the new junction table, skipping redundant partner entries:

-- Insert non-redundant category links first
INSERT INTO `partner_category_links` (`partner_id`, `category_id`, `subcategory_id`, `subcategory2_id`)
SELECT `partner_id`, `category_id`, `subcategory_id`, `subcategory2_id`
FROM `partner`
WHERE `redundancy` = 1;

-- Add the link for the redundant Udo Förster entry (from partner_id=9)
INSERT INTO `partner_category_links` (`partner_id`, `category_id`, `subcategory_id`, `subcategory2_id`)
VALUES (2, 2, 13, 15);

-- Delete redundant partner entries from the main table
DELETE FROM `partner` WHERE `redundancy` = 0;

2. PHP Data Retrieval Examples #

Get a partner with all their category paths #

Use JOINs to fetch a partner and all associated category combinations in one query:

<?php
// Assume $pdo is your established PDO connection
$partnerId = 2; // Udo Förster's ID

$sql = "
SELECT p.*, c.category_name, sc.subcategory_name, sc2.subcategory2_name
FROM partner p
JOIN partner_category_links pcl ON p.partner_id = pcl.partner_id
JOIN categories c ON pcl.category_id = c.category_id
JOIN subcategories sc ON pcl.subcategory_id = sc.subcategory_id
JOIN subcategories2 sc2 ON pcl.subcategory2_id = sc2.subcategory2_id
WHERE p.partner_id = :partnerId AND p.status = 1
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['partnerId' => $partnerId]);
$partnerData = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Output results
foreach ($partnerData as $entry) {
    echo "<h3>" . htmlspecialchars($entry['partner_name']) . "</h3>";
    echo "Category Path: " . htmlspecialchars($entry['category_name']) . " > " . htmlspecialchars($entry['subcategory_name']) . " > " . htmlspecialchars($entry['subcategory2_name']) . "<br>";
}
?>

Get all unique partners in a specific category #

Use DISTINCT to ensure no duplicate partner results:

<?php
$categoryId = 2;

$sql = "
SELECT DISTINCT p.*
FROM partner p
JOIN partner_category_links pcl ON p.partner_id = pcl.partner_id
WHERE pcl.category_id = :categoryId AND p.status = 1
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['categoryId' => $categoryId]);
$partnersInCategory = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

Why This Works Better #

• No data redundancy: You only store each partner once, even if they belong to multiple categories
• Easier maintenance: Update a partner's details once, not across multiple duplicate entries
• Faster queries: Indexes on the junction table make category/partner lookups efficient
• Scalable: Add new category links without modifying the core partner table structure

内容的提问来源于stack exchange，提问作者mivanov