Multi-Mapping: How to Manage ‘One-to-Many’ Relationships in Databases?

Hello dear tech friends! How are you these days? I’m back at the code, juggling a thousand projects in my mind. Recently, a topic has been on my mind especially when working with database relationships: Multi-Mapping, also known as ‘Many-to-Many’ relationships. You know, sometimes a record in one database table needs to connect to multiple records in another table, and that’s where things can get a bit complicated. Quite fascinating, isn’t it? Looks simple but actually quite deep.

Thinking back, I don’t remember exactly when I first got interested in this, but I think it was during work on an e-commerce site. A product needed to belong to multiple categories. For example, a ‘Laptop’ should be in both ‘Computers’ and maybe also in ‘Electronics’. At the same time, many other products are in the ‘Computers’ category. That’s when I realized that establishing this ‘many-to-many’ relationship requires an ‘intermediate’ table. Think of it like a bridge connecting two sides, giving structure and meaning to the connection.

This linking table can be called a ‘junction table’ or ‘relation table’. Its function is consistent: in it, one side stores the main table, the other side the related table, and between them, this structure holds the IDs from both sides. This way, a product can connect to as many categories as needed, and a category can have many products. Isn’t that wonderful? So, we are no longer limited by constraints like ‘a product belongs to only one category’.

So, how do we do this practically? Usually, ORM (Object-Relational Mapping) tools make this easier. For example, in C# with Entity Framework Core, defining this multi-mapping is quite straightforward. You have two main entities (say, `` and ``) and specify that there’s a ‘many-to-many’ relationship between them. Entity Framework Core automatically manages this intermediate table and relationship mappings. If you use lightweight ORMs like Dapper, this relationship needs to be managed manually. But don’t worry, it has its own advantages, especially in performance.

Now, let’s look at the code side. Suppose we have a `Product` table and a `Category` table. A product can be in multiple categories, and each category can contain many products. To establish this ‘many-to-many’ relationship, we’ll introduce a `ProductCategory` table. This table will have two columns: `ProductId` and `CategoryId`. Each row links a specific product to a specific category.

Here’s an example of how to do this with C# and Dapper. First, set up the database tables to keep things simple:

-- PostgreSQL Example
 CREATE TABLE Categories (     CategoryId SERIAL PRIMARY KEY,     CategoryName VARCHAR(100) NOT NULL );
 CREATE TABLE Products (     ProductId SERIAL PRIMARY KEY,     ProductName VARCHAR(100) NOT NULL,     Price DECIMAL(10, 2) );
 -- Many-to-Many Relationship Table
 CREATE TABLE ProductCategory (     ProductId INT REFERENCES Products(ProductId),     CategoryId INT REFERENCES Categories(CategoryId),     PRIMARY KEY (ProductId, CategoryId) -- Prevents the same product and category from linking multiple times );

After creating these tables, let’s see how to handle this relationship in code. Say we want to retrieve all categories for a product. Usually, with Dapper, you’d write more complex queries. But I’ll show you both an ‘incorrect’ way (many separate queries, performance issues) and a ‘correct’ way.

Here’s an example from my old code. Those days, I was just learning and used to query separately for each relationship. Then I realized this hurt performance and made the code messy. I won’t show that ‘bad’ approach now since that code no longer exists. Instead, I’ll show a good way to fetch related data in one query using Dapper. We can join the main table (`Products`), the intermediate table (`ProductCategory`), and the related table (`Categories`) with a SQL query. Dapper’s ‘SplitOn’ feature is very helpful here, allowing us to get both products and their categories in one go.

Consider this code:

public class ProductWithCategoriesViewModel {     public int ProductId { get; set; }     public string ProductName { get; set; }     public decimal Price { get; set; }     public List<Category> Categories { get; set; } = new List<Category>(); }

 public class Category {     public int CategoryId { get; set; }     public string CategoryName { get; set; } }
 // Database access method public async Task<IEnumerable<ProductWithCategoriesViewModel>> GetProductsWithCategoriesAsync(IDbConnection db) {     var sql = @"         SELECT             p.ProductId,             p.ProductName,             p.Price,             c.CategoryId,             c.CategoryName         FROM Products p         LEFT JOIN ProductCategory pc ON p.ProductId = pc.ProductId         LEFT JOIN Categories c ON pc.CategoryId = c.CategoryId         ORDER BY p.ProductId;     ";
     var products = await db.QueryAsync<ProductWithCategoriesViewModel, Category, ProductWithCategoriesViewModel>(sql, (product, category) =>     {         if (category != null)         {             product.Categories.Add(category);         }         return product;     }, splitOn: "CategoryId"); // Instructs Dapper to split the data starting from 'CategoryId'
     var groupedProducts = products.GroupBy(p => p.ProductId)                                   .Select(g =>                                   {                                       var product = g.First();                                       product.Categories = g.SelectMany(p => p.Categories)                                                          .Where(c => c != null) // Filter out nulls                                                          .ToList();                                       return product;                                   });
     return groupedProducts; }
In this code, `QueryAsync` fetches data into `ProductWithCategoriesViewModel` and `Category` objects. The `splitOn: "CategoryId"` tells Dapper that columns after 'CategoryId' belong to the `Category` object. Then, the grouping operation organizes multiple categories under the same product. Isn't that neat? One query, combining data from three tables and the junction table.
This multi-mapping is actually a core concept in database design. Especially for complex relationships, it makes things much easier. Otherwise, writing separate queries for each relationship increases code complexity and impacts performance. So, establishing and managing these relations correctly speeds up development and results in a more stable, performant project. If you're interested in exploring this further, search for Dapper multi-mapping tutorial.
Of course, every technology has its nuances, such as proper indexing and query optimization for performance. But the main idea is this: don't hesitate or shy away from using junction tables in complex relationships! In fact, embrace them!
Recently, my wife and I attended a camping event. We went up to Uludağ in Bursa. The weather was freezing but the view was extraordinary. Sitting by the campfire, watching the stars, I wondered how many database tables perform so harmoniously. Maybe, just like the perfect harmony in this gathering, building a structure with every piece in its right place is essential. Think of it like a jigsaw puzzle.
Hopefully, this explanation has sparked some insight in your mind. Multi-mapping is a truly valuable topic to master. If you deal with database relationships, try this approach. If you're using Entity Framework Core, it becomes even easier. With Dapper, it's equally possible but requires more attention.
Remember, coding isn't just about issuing commands; it's an art of problem solving. Managing data most efficiently is part of this art. Multi-mapping is exactly that—an intelligent way to bring data together. :) Got it? Hope so.