Retriving and displaying matrix data

[NotionCommotion]

EDIT.  Never mind.  I should be able to figure this out using GROUP_CONCAT and a loop.  Can't seem to delete the post, however.

 

I would like a generic approach to store and display matrices such as the following.

 

 

To store the data, I am thinking of the following schema.  While this is a PHP question, if you feel I haven't modeled it correctly, please advise.

 

 

 

Given the chart ID, how would it be best to retrieve the data and create the table?

Edited August 6, 2016 by NotionCommotion

[Barand]

According to that model, a chart can have many categories but a category can belong to only one chart.

[NotionCommotion]

According to that model, a chart can have many categories but a category can belong to only one chart.

Agreed, however this is acceptable. Thanks

[Barand]

So if your categories are the continents then you could have a chart showing population growth, say, but you could not then have another chart for those continents showing economic growth.

[NotionCommotion]

Data is user inputted and if they want, they will have to re-input data for the next chart, however they will most likely not want to.

 

Actually I am still stumped as the cross table may not have a record for a given series/category and I therefore can't always join series and.categories.

[Barand]

perhaps

                      +-------------+
                      |    chart    |
                      +-------------+
+-------------+              |               +---------------+
|   series    |              |               |    category   |
+-------------+              |               +---------------+
       |                     |                      |
       |                    /|\                     |
       |              +-------------+               |
       +-------------<|    value    |>--------------+
                      +-------------+

[NotionCommotion]

Thanks Barand, but this would require a value for each series or category.  Guess I will just do it in two queries: one to get say the series with a left outer join for values, and group_concate the values, and a second query for the categories.