Rows vs Columns / matrix

[biota]

Hi all 

My app is essentially moving a survey from MS Word, to a smart form with autocomplete fields etc. I am currently figuring out app logic, after which the whole admin side can be built on top hopefully.

The Word doc has several 'tables' that don't really conform to entity vs attribute data normalisation. Essentially the first column of the tables is kind of a subheading of the column heading, rather than an attribute.

It may be more accurate to call this a matrix, rather than a table?

Anyway, using the standard DP reports, the only way I can see to manipulate and display the data is to have each new instance of a survey fire a Trigger to duplicate a dummy/template table, whereby the first column contains the subheadings, and the subsequent data can be added/edited.

Is this the wrong way to go about this and/or likely to have other consequences?

It seems like quite a conundrum. 

TIA

[vidierre]

@biota, it is hard to answer whitout knowing the data nature. Each table has the same headings? What kind of data there is into the intersections? What represent the subheadings?

Matrix or tables aren't a simple positioning of data on the floor. Let me make an example: suppose this is your matrix:

fig.1

Your column labels are a sort of types of vegan meals and row labels are the source where they are produced. The yellow fields represent how much each is consumed.
It means tah we have three coherent sets of data and they could be repressented in tabular format in this way:

    Here you have the columns and rows labels spread as data into table's column. 
fig.2

But considering the unique nature of row labels (in this example are continents), we could represent them also in tabular format this way

   Where a name that characterizes row labels (continents) make one column. 
fig.3

And what about the nature of yellow fields? because they couldn't be a single data but the results of some calculations. Just to give a taste of what I mean, it could be obtained from this table:

   Where the SUM of differents amount became the yellow fields.
fig.4

As you can see the same matrix could be the results of differents tables representing data.
My thought is that you could fit, for each table in your word, in the fig.4 case. So you need to use a table to representing your surveys with a uniquie ID for each survey and data survey-relatde (when it was done, who did it, etc.). Than a separate table for each of your matrix/tables insinde the surveys with an extra column related to the survey_id.

 

[biota]

Thanks a lot for this @vidierre! I have only just seen your reply.