How to Design Elasticsearch Index Structure for N:M Relationships?

Setting Up Elasticsearch Index Structure for Multiple Entity Bindings

Introduction

Integrating Elasticsearch (ES) into existing applications can be a daunting task. This dialogue addresses a challenge in setting up ES index structure when dealing with multiple entity bindings in a legacy database.

Database Structure

The provided database structure presents three tables: Products, Flags, and flagsProducts, which represents an N:M relationship between products and flags. The goal is to replicate this structure in ES while optimizing for efficient querying.

Recommended Approach: Flattening

Rather than maintaining the N:M relationship, it's recommended to flatten the structure and create product documents that embed flag information. This approach simplifies queries and improves data access.

Product Document Structure

The flattened product document would contain the following fields:

• id: Product ID (string, not_analyzed)
• title: Product title (string)
• price: Product price (double, null_value: 0.0)
• flags: Array of flag titles (string, not_analyzed)

Example Product Documents:

{
   "id": "00c8234d71c4e94f725cd432ebc04",
   "title": "Alpha",
   "price": 589.0,
   "flags": ["Sellout", "Top Product"]
}
{
   "id": "018357657529fef056cf396626812",
   "title": "Beta",
   "price": 355.0,
   "flags": ["Discount"]
}

Product Mapping Type

The corresponding mapping type in ES would be:

PUT products
{
    "mappings": {
        "product": {
            "properties": {
                "id": {
                    "type": "string",
                    "index": "not_analyzed"
                },
                "title": {
                    "type": "string"
                },
                "price": {
                    "type": "double",
                    "null_value": 0.0
                },
                "flags": {
                    "type": "string",
                    "index": "not_analyzed"
                }
            }
        }
    }
}

Fetching Data from Database

To retrieve the necessary data from the database for ingestion into ES, use the following SQL query:

The above is the detailed content of How to Design Elasticsearch Index Structure for N:M Relationships?. For more information, please follow other related articles on the PHP Chinese website!