# Convert relational schema to elasticsearch mapping

**URL:** <https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291>\
**Category:** Elasticsearch\
**Created:** [January 20, 2017, 12:25pm UTC](https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291 "2017-01-20T12:25:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jagan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jagan/32/42200_2.png) [@jagan](https://discuss.elastic.co/u/jagan)\
**Post date:** [January 20, 2017, 12:25pm UTC](https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291/1 "2017-01-20T12:25:47Z")

</div>

Hi All,  
I am trying to convert the below relational schema to elasticsearch mapping. please correct me if it is the correct approach or suggest me for best mapping model.

![](https://us1.discourse-cdn.com/elastic/original/2X/9/92a8f8b99cf46670cbca3eff7b7003129cdb78a0.jpg)

```auto
POST /empdb/empdata/
 {
 	"mappings": {
 		"properties": {
 			"employees": {
 				"properties": {
 					"emp_no": {
 						"type": "integer"
 					},
 					"birthdate": {
 						"type": "timestamp"
 					},
 					"first_name": {
 						"type": "string"
 					},
 					"last_name": {
 						"type": "string"
 					},
 					"gender": {
 						"type": "string"
 					},
 					"hire_date": {
 						"type": "timestamp"
 					}
 				}
 			},
 			"dept_emp": {
 				"properties": {
 					"emp_no": {
 						"type": "integer"
 					},
 					"dept_no": {
 						"type": "string"
 					},
 					"from_date": {
 						"type": "timestamp"
 					},
 					"to_date": {
 						"type": "timestamp"
 					}
 				}
 			},

 			"salaries": {
 				"properties": {
 					"emp_no": {
 						"type": "integer"
 					},
 					"salary": {
 						"type": "long"
 					},
 					"from_date": {
 						"type": "timestamp"
 					},
 					"to_date": {
 						"type": "timestamp"
 					}
 				}
 			},
 			"dept_manager": {
 				"properties": {

 					"dept_no": {
 						"type": "string"
 					},
 					"emp_no": {
 						"type": "integer"
 					},
 					"from_date": {
 						"type": "timestamp"
 					},
 					"to_date": {
 						"type": "timestamp"
 					}
 				}
 			},

 			"titles": {
 				"properties": {
 					"emp_no": {
 						"type": "integer"
 					},
 					"title": {
 						"type": "string"
 					},
 					"from_date": {
 						"type": "timestamp"
 					},
 					"to_date": {
 						"type": "timestamp"
 					}
 				}
 			},

 			" departments": {
 				"properties": {
 					"dept_no": {
 						"type": "integer"
 					},
 					"dept_name": {
 						"type": "integer"
 					}

 				}
 			}
 		}
 	}
 }

```

Thanks,  
Jagan

---

<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:** [January 20, 2017, 12:54pm UTC](https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291/3 "2017-01-20T12:54:55Z")

</div>

In short:

What do you want to search for? Employees? Departments? Salaries? I mean what is the single unit which should come as a response to your user?

If it's an employee, than index only employees.

Second question is: what do I need to search my employees?  
If you want to search an employee by its department name, then add a department name within your employee document.

I'd encourage reading: [https://www.elastic.co/guide/en/elasticsearch/guide/current/modeling-your-data.html](https://www.elastic.co/guide/en/elasticsearch/guide/current/modeling-your-data.html)

---

<div class="post-metadata">

**Author:** ![jagan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jagan/32/42200_2.png) [@jagan](https://discuss.elastic.co/u/jagan)\
**Post date:** [January 20, 2017, 1:19pm UTC](https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291/4 "2017-01-20T13:19:58Z")

</div>

Thanks for the quick response David.

Actually i had the same table structure(8 tables) where i want to store user, password and configuration information((Projects) in elasticsearch indexes.

So do i need to map all the tables&columns in Elasticsearch as done above for emp info or just create mapping for particular tables only?  
I am curious to understand how does the join happens between tables?If i manage to create a mapping as above for emp schema for all tables Is it the correct approach?  
So as per your suggestion we need to index tables which are used for Search only and skip the mapping for other tables which are not used for search?

Thanks,  
Jagan

---

<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:** [January 20, 2017, 1:40pm UTC](https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291/5 "2017-01-20T13:40:58Z")

</div>

You need to denormalize your data and think "object" or "document" instead of "tables".  
There is no join in elasticsearch.

Small example. Let say I have a user who has a name and who is living in a country.

In SQL DB, I'd probably have 2 tables:

- User

- Country

Now, in elasticsearch, I'd wrote a User document as:

```auto
{
  "name": "David",
  "country": {
     "name": "France"
  }
}

```

Then, I'd be able to search a User by its name or by its country as I have all information I need in it.

But read the doc I linked to.

---

<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:** [February 17, 2017, 1:41pm UTC](https://discuss.elastic.co/t/convert-relational-schema-to-elasticsearch-mapping/72291/6 "2017-02-17T13:41:12Z")

</div>

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