Looking for input on the best way to structure my data. I manage 10's of 1,000's of assets. Each asset has a net income by month. So for example:
Asset | Jan | Feb | March | April |
---|
1 | 100 | 200 | 300 | 400 |
2 | 200 | 300 | 100 | 500 |
If I wanted to display a chart, or do any type of query what is the best way to structure the data. The example above has the columns as integers or doubles. Another method could be below.
Date | Asset | Income |
---|
01/01/2019 | 1 | 100 |
01/01/2019 | 2 | 200 |
02/01/2019 | 1 | 200 |
02/01/2019 | 2 | 300 |
This would allow date field searches and structure. But would duplicate the fields. I manage over 100,000 assets, so for one calendar year I would have a table with over a million records.
Any ideas on the best way to structure my data. Ultimately, I would like to create charts showing monthly income in a web app or through Ops Dashboard