Storing reports with a lot of rows in relational database (Oracle)

oracleperformanceschema

I am setting up a relational database (Oracle) to store some reports with the following specifications:

  • Each report has the same columns (say 20 columns of strings or numbers). I expect the columns to be rarely changed but they might change.
  • Each report has a lot of rows (~200k).
  • The report generation frequency is like once every few days.

I have this schema in mind where report_id and row_id is the composite primary key:

+---------------+---------------+-----+
|    Column     |     Type      |     |
+---------------+---------------+-----+
| report_id     | uuid (string) | KEY |
| row_id        | number        | KEY |
| data_column_1 | string        |     |
| data_column_2 | number        |     |
| ...           | ...           |     |
+---------------+---------------+-----+

To retrieve a report, I will do something like

SELECT * FROM report_rows WHERE report_id = 'SOME-UUID';

Is this an appropriate design? Will performance suffer when there are more reports? If so, is there a better schema / other database to store data like this?

Thanks.

Best Answer

Some things to consider are;

  • How is the data populated, in one operation or over a period of time ?
  • How you access the data, e.g. how does a user know that report_id 32645 is the one they want ?
  • Will you need the data in a different order ?
  • Will one reports data ever be correlated with another reports data ?
  • Will data for a report_id ever need to be removed ? If so how do you identify it ?
  • If you use generic column names be prepared to confuse some people.

This sort of solution CAN work but like any solution it has some limitations (some of which can be seen from the above questions). Without knowing what sort of reports are stored its very hard to guess but in my experience reports tend to change quite frequently based on a customers needs.

They will see some data and that will lead them on the path to refining and expanding what they want to see - and that's a good thing.

Ideally you store the data in a way that reflects how it sits in the organisation (as you would with any modelling) and the reporting solution sits above that pulling out the information in whatever way it wants. This is what a data warehouse/reporting database is designed for.

Related Question