# Efficient document structure
**URL:** https://discuss.elastic.co/t/efficient-document-structure/240760
**Category:** Elasticsearch
**Created:** [July 10, 2020, 8:21pm UTC](https://discuss.elastic.co/t/efficient-document-structure/240760 "2020-07-10T20:21:42Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@maxheyer](https://discuss.elastic.co/u/maxheyer)
#### Post date: [July 10, 2020, 8:21pm UTC](https://discuss.elastic.co/t/efficient-document-structure/240760/1 "2020-07-10T20:21:42Z")
Hello,
we want to use elastic search for e-commerce statistics. In our web view we have the possibility to filter products by sales, attributes like colors, categories, full text search etc.
The result is a table of products with statistics on sales, inventory changes and product attributes.
With the current implementation we calculate sales kpis on indexing for a period of 90 days, but in the future we want to calculate them on-the-fly. Our mysql schema looks like this:
```auto
Product models 1:n Product variants 1:n Product units
Orders 1:n Order lines n:n Product units
```
For your information: The table of order lines has 6 million lines and a total size of 2 GB. The product tables look similar.
Is there an smart way to index all data with relations in the Elastik search? For example, should we store all data in one document: A product document has all attributes and a list of all orders and stock changes?
Or should we split it into different documents? Is there a possibility to join them then?
---
### Author:  [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)
#### Post date: [July 12, 2020, 4:00am UTC](https://discuss.elastic.co/t/efficient-document-structure/240760/2 "2020-07-12T04:00:55Z")
There is only limited support for Join in Elasticsearch. If using Elasticsearch, you should de-normalize the dimension table directly into the fact table to avoid join.
---
### Author:  [@fadjar340](https://discuss.elastic.co/u/fadjar340)
#### Post date: [July 12, 2020, 8:57am UTC](https://discuss.elastic.co/t/efficient-document-structure/240760/3 "2020-07-12T08:57:07Z")
Use View or Materialized View with all of the data that oyu interested into de-normalized, then ingest the view using pagination and timestamp \> :sql\_last\_value to avoid the abuse of select query.
---
### Author:  [@nik9000](https://discuss.elastic.co/u/nik9000)
#### Post date: [July 12, 2020, 7:11pm UTC](https://discuss.elastic.co/t/efficient-document-structure/240760/4 "2020-07-12T19:11:19Z")
If your orders are already in mysql I'd leave then there. I'd calculate what you need in MySQL and dump those fields into the document. ES stores the whole document together for fast retrieval of all fields. So denormalizing all the orders into the document would make loading slow.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 9, 2020, 7:11pm UTC](https://discuss.elastic.co/t/efficient-document-structure/240760/5 "2020-08-09T19:11:42Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.