The best way to store rows of the table of a document(SQL)?

I have a report that has a table.

The report also has fields.

For example(JSON):

{
   company: Company,
   period: "3M2019",
   typeReport: "type",
   multiplicity: 1000
   currency: "USD",
   rows: [
       { parameter: parameter1, value: 1000 },
       { parameter: parameter2, value: 2000 },
       { parameter: parameter3, value: 3000 },
   ]
}

I have two scenarios for getting data:

1. Getting all rows of the selected report;
2. Getting all rows(from all reports) of all or selected parameters of periods, typeReport, currency, Company.

If the first scenario is understood, the second scenario has two ways of storage:

1. More reading from database

   Company, period, typeReport currency are stored in the Report table and when I want to get rows I should find all reports with these fields and then find all rows by reports and parameters.

2. More stored data

   Company, period, typeReport, currency are stored in the Row table in each rows. Then I can find each row by fields to do one query to one table.

For example row in database:

{company: Company, period: “3M2019”, typeReport: “type”, multiplicity: 1000, currency: “USD”, parameter: parameter1, value: 1000},

{company: Company, period: “3M2019”, typeReport: “type”, multiplicity: 1000, currency: “USD”, parameter: parameter2, value: 2000},

{company: Company, period: “3M2019”, typeReport: “type”, multiplicity: 1000, currency: “USD”, parameter: parameter3, value: 3000},

What is the best way to store data: more data or more reading from the database?

Answer

One simple way to store data for this tree-like data structure (with an apparent 1-n relationship) could be:

table 1: base with columns

id, company, period, typeReport, multiplicity, currency

The column id is a unique (primary) key for this table. Typically it will be an auto-incremented column.

table 2: params with columns:

tid, parameter, value

You can then get all data (or search for any column value) with

select company, period, typeReport, 
       multiplicity, currency, 
       parameter, value 
from base
inner join params on tid=id
-- where parameter=...

Using this type of data structure you can make sure that there are no redundancies and that no conflicting data will ever be stored.