Convert relational schema to elasticsearch mapping

[jagan (jagdish)]

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.

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

[dadoonet (David Pilato)]

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

[jagan (jagdish)]

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

[dadoonet (David Pilato)]

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

  • id
  • name
  • country_id

• Country

  • id
  • name

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

{
  "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.