# Which is the best way to index the data from relational database

**URL:** <https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870>\
**Category:** Elasticsearch\
**Created:** [July 27, 2018, 4:56am UTC](https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870 "2018-07-27T04:56:22Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![girishts](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/girishts/32/33789_2.png) [@girishts](https://discuss.elastic.co/u/girishts)\
**Post date:** [July 27, 2018, 4:56am UTC](https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870/1 "2018-07-27T04:56:22Z")

</div>

Hi ,

Can you please let me know which is the best way to index the records in elastic search for my scenario.

My Scenario is :

1. Need to index around 40 million records from oracle table which has entries having one to many relationship records. And the uniqueness of the records is based on the composite key with 4 columns

2. After indexing , Search should support "full text search" on all the fields

3. Filters and sorting on selected fields needs to be supported.

After going through the official documentation i found couple of options , but want to know which approach would be most useful among below

1. For each record in table create a entry in the elastic index
2. Create a nested json object based on the composite key and then add this elastic index
3. Parent child Relationship mechanism and application side joins are not suitable for my scenario

Thanks  
Girish T S

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 27, 2018, 8:26am UTC](https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870/2 "2018-07-27T08:26:24Z")

</div>

My advice:

Think about the use case, not the current implementation.  
Basically ask yourself: "What type of data my user will be searching for?".

As an example, let's say that users want to search for employees. Then index employees.

2nd question is "What kind of attributes do my users will use to search?". Let's say "company name", "company website" and "employee name". Then just store those values within each document, like:

```auto
PUT employees/_doc/david
{
  "name": "David XYZ",
  "company": {
    "name": "elastic",
    "website": "https://elastic.co"
  }
}

```

So don't try to reimplement relational model if it's not absolutely needed but just focus first on the use case.

For the record I shared most of my thoughts there: [http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/](http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/)

---

<div class="post-metadata">

**Author:** ![girishts](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/girishts/32/33789_2.png) [@girishts](https://discuss.elastic.co/u/girishts)\
**Post date:** [July 27, 2018, 10:13am UTC](https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870/3 "2018-07-27T10:13:07Z")

</div>

```
Thanks for the reply David.

```

Please let me know whether the nested type will be suitable.

My object definition looks like below..

```
 {
      "primarDetails": {
               "attr1": "",
               "attr2": "",
               "attr3": ""
       },
  "AdditionalDetails": {
    "AdditionalDetailsList": [
      {
        "countryId": "",
        "countryName": "",
        "objOneDetails": {
          "objOneDetailsList": [
            {
              "x": "",
              "y": "",
              "z": ""
            },
            {
              "x": "",
              "y": "",
              "z": ""
            }
          ]
        },
        "objTwoDetails": {
          "objTwoDetailsList": [
            {
              "a": "",
              "b": "",
              "c": "",
              "d": ""
            },
            {
              "a": "",
              "b": "",
              "c": "",
              "d": ""
            }
          ]
        }
      }
    ]
  }
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 27, 2018, 10:52am UTC](https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870/4 "2018-07-27T10:52:31Z")

</div>

I can't tell with a real example. May be. May be not.

---

<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:** [August 24, 2018, 10:52am UTC](https://discuss.elastic.co/t/which-is-the-best-way-to-index-the-data-from-relational-database/141870/5 "2018-08-24T10:52:39Z")

</div>

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