# Insert and update relational data with denormalization

**URL:** <https://discuss.elastic.co/t/insert-and-update-relational-data-with-denormalization/202556>\
**Category:** Elasticsearch\
**Created:** [October 7, 2019, 3:08pm UTC](https://discuss.elastic.co/t/insert-and-update-relational-data-with-denormalization/202556 "2019-10-07T15:08:38Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![meynety](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@meynety](https://discuss.elastic.co/u/meynety)\
**Post date:** [October 7, 2019, 3:08pm UTC](https://discuss.elastic.co/t/insert-and-update-relational-data-with-denormalization/202556/1 "2019-10-07T15:08:38Z")

</div>

Hello,

As a new ES user, I'm facing some questions about data structure, denormalization and what comes with it :

Considering a simple 1:n data structure, like `Device` -\> `Record`, where a `Device` produces a high number of `Record` (1M).

Trying to denormalize the data (and considering the removal of "types"), I imagined the following 2 indexes mapping on ES :

Index `Device` mapping :

```
{
  "mapping": {
    "_doc": {
      "properties": {
        "deviceId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "location": {
          "type": "geo_point"
        },
        "name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "upStatus": {
          "type": "boolean"
        }
      }
    }
  }
}

```

Index `Record` mapping :

```
{
  "mapping": {
    "record": {
      "properties": {
        "timestamp": {
          "type": "date"
        },
        "type": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "device": { 
            "properties": {
              "deviceId": {
                "type": "text"
              },
              "location": {
                "type": "geo_point"
              },
              "name": {
                "type": "text"
              },
              "upStatus": {
                "type": "boolean"
              }
            }
         }
      }
    }
  }
}

```

I'm facing two problems with this :

1. A client that wants to index records have to send all the `Device` properties along with the `Record`  
-\> I expected there was a way to solve this using [the ingest node](https://www.elastic.co/guide/en/elasticsearch/reference/current/ingest.html#ingest) but I could not find a [Processor](https://www.elastic.co/guide/en/elasticsearch/reference/current/ingest-processors.html#ingest-processors) that can fetch the necessary `Device` properties to add them to the `Record`.
2. Data integrity : each time a property on `Device` changes, all corresponding `Records` (\> 1M) needs to be updated.

Do you have any pointers on those 2 issues or maybe a better data structure, etc… ?

Thank you very much for your inputs !

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [October 9, 2019, 11:33am UTC](https://discuss.elastic.co/t/insert-and-update-relational-data-with-denormalization/202556/2 "2019-10-09T11:33:42Z")

</div>

1. You're right - there's no such processor - yet. Work is underway on a `decorate` processor that will allow you to do this with an ingest pipeline. You can follow that work on [this Github issue](https://github.com/elastic/elasticsearch/issues/32789). For now, Logstash is probably the better tool to achieve this.

2. Yeah, updating all the records could be done using an [update by query](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-update-by-query.html), but it's very expensive. Another solution would be to index parent-child documents using the [`join` datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html). This would allow you to change the devices without having to update the records. However, be aware that joining the devices and their records at query time, with [`has_parent`](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-has-parent-query.html) and [`has_child`](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-has-child-query.html) queries will be much slower than querying your current flat denormalized documents. Everything comes at a price.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 6, 2019, 11:33am UTC](https://discuss.elastic.co/t/insert-and-update-relational-data-with-denormalization/202556/3 "2019-11-06T11:33:43Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.