I’m working with a database that has authors and their books. My table looks something like this:
writer_id novel_id(PK) novel_name additional_data
========================================================
5 101 Ocean etc.
5 102 Storm etc.
8 103 Blue etc.
8 104 Purple etc.
I want to compare every book from Writer5 against every book from Writer8. I need to calculate a match_score for each pair. The goal is to build a result table like this:
comp_id(PK) novel_id1 novel_id2 match_score
=================================================
1 101 103 Medium
2 101 104 High
3 102 103 High
4 102 104 Low
Since the comparison works both ways (comparing 101 vs 103 gives the same result as 103 vs 101), I think I only need unique pairs. But maybe it’s better to store all combinations including duplicates? That way when searching for a specific book ID, I only need to check one column instead of two. What approach do you recommend?
Both approaches work, but I’d go with storing all combinations (duplicates included). You’re right - it makes queries way faster when you only need to check one column.
The real pain isn’t the database structure though. It’s calculating those match scores. Are you doing this manually or building something automated?
I hit this same problem building a content comparison system. Instead of fighting with complex SQL joins and manual scoring, I used Latenode to automate the whole thing.
The workflow grabs data from MySQL, creates all possible pairs automatically, runs whatever comparison logic you need (text similarity, feature matching, custom scoring), then dumps results back into your comparison table. Set it to run on schedule or trigger when you add new books.
Best part? You can test both storage approaches easily. Start with unique pairs, and if queries get slow, just tweak the automation to generate duplicates.
Automation really pays off for scoring. You can plug in different comparison APIs, use multiple scoring criteria, even A/B test algorithms without messing with your core database.
The storage choice depends on your query patterns, but also think about data volume and maintenance overhead. I built a book recommendation engine where we started with unique pairs only. Seemed efficient at first, but queries became a nightmare when we needed bidirectional lookups. We switched to storing both directions after realizing 2x storage cost was nothing compared to the performance boost. The real pain wasn’t the approach though - it was managing incremental updates. Every time you add new books, you’ve got to generate comparisons against all existing books from other writers. If you’re planning to scale beyond two writers, partition your comparison table by writer pairs. Also, depending on how you calculate match_score, separate pair generation from scoring. We ended up with a two-step process - create all pairs with null scores first, then update scores in batches. Handles failures better and lets you recalculate scores without regenerating pairs.
honestly just use CROSS JOIN like miat suggested but add WHERE a.novel_id < b.novel_id to avoid duplicates. way cleaner than managing 2x storage and you can always add both directions later if queries get slow. start simple first
I’ve handled similar cross-comparison setups before. Go with the duplicate approach - storage is cheap, but query performance matters.
Here’s the SQL I’d use with a CROSS JOIN on filtered subsets:
INSERT INTO comparisons (novel_id1, novel_id2, match_score)
SELECT a.novel_id, b.novel_id, calculate_match_score(a.novel_name, b.novel_name)
FROM novels a
CROSS JOIN novels b
WHERE a.writer_id = 5 AND b.writer_id = 8;
This creates all combinations in one shot. But here’s where it gets tricky - keeping everything in sync when books get added or deleted. I learned this lesson when my comparison table went stale after updates.
Set up triggers or scheduled jobs to refresh comparisons automatically. And plan your indexing early - you’ll need composite indexes on both novel_id columns based on how you query. Trust me, doing this upfront beats dealing with performance issues later as your data grows.