I'm completley new to Elasticsearch but I run a website with quoite a lot of data and just MySQL doesn't do it for me anymore.
I would love some help on how to set up my index. My site has users, each user can create as many blogs they want to. A blog has posts. Every post has a location_id. The locations table have many locations.
Every user will have a currently_in_location_id from where they were last as well as a from_location_id that tells where they are from.
The locations table will rarely change, but it will get things added. But users currently_in_location_id will change every few days.
I'm not sure how i should build the index, especially in regards to the locations as a posts location could be "Brazil", or it could be "Copenhagen, Denmark" or "Panjim, Goa, India"
Below i have tried to give an examble of how my data can look:
STRUCTURE OF THE DATA
USERS
id
username
firstname
lastname
from_location_id
currently_in_location_id
date_of_birthLOCATIONS
location_id
parent_location_id
location_name
location_geo_lat
locatoin_geo_lng
location_typeBLOGS
blog_id
user_id
blog_titlePOSTS
post_id
blog_id
user_id
location_id
post_geo_lat
post_geo_lng
post_title
post_body
published_at
updated_at
BELOW IS SOME SAMPLE DATA
USER
user_id: 1
username: bob
firstname: bob
lastname: bobson
from_location_id: 7
currently_in_location_id: 2
email: bob@bob.com
date_of_birth: 1985-10-26LOCATION (There is no predefined depht here, the child relations goes on for 0-10 levels)
location_id: 1
parent_location_id: NULL
location_name: Russia
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: COUNTRYlocation_id: 2
parent_location_id: 1
location_name: Moscow
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: CITYlocation_id: 3
parent_location_id: NULL
location_name: Sweden
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: COUNTRYlocation_id: 4
parent_location_id: 3
location_name: Stockholm County
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: COUNTYlocation_id: 5
parent_location_id: 4
location_name: Stockholm
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: CITYlocation_id: 6
parent_location_id: 5
location_name: Gamla Stan
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: NEIGHBORHOODlocation_id: 7
parent_location_id: 3
location_name: Kalmar
location_geo_lat: 0
locatoin_geo_lng: 0
location_type: CITYBLOG
blog_id: 1
user_id: 1
blog_title: Bob's blogPOST
post_id: 1
blog_id: 1
user_id: 1
location_id: 2
post_geo_lat: 0
post_geo_lng: 0
bost_title: Hello Moscow
post_body: I have now arrived in Moscow, it's great
published_at: 2016-20-21 13:37:00
updated_at: 2016-10-21 00:42:00
Any advice on how to build my index for this data is appreciated!