Database structure to display data in graphs

Hi Everyone,

Sorry if this has been answered elsewhere, I tried searching but I dont know what the right key words I should be searching for are to answer this question.

In summary, I want to use a Donut Graph to visualize the relative proportion of three values compared to the sum of all three values. In my case, I would like to graph the number of grams of Protein, Fat and Carbs a client is allocated on a given nutrition plan. To give you an example of how it would be represented please see the below image.

Graphing this data is easy, I would query nutrition plans where client = Current user, then display the “Type” (being protein, Fat, Carbs) as the Series label, and the grams allocated as the Series Values. In the below image this is represented by the Database Schema “how the graph wants the data”

Unfortunately, for my purposes storing individual values within the database as separate entries is highly inefficient and would result in significant Read/Write delays as my user base grows.

Because of this, my data is organised as a single Nutrition plan which contains seperate values for protein, fat, carbs and user. This allows me to retrieve all nutrition information in a single read function. Unfortunately this data schema is not readable as a graphing or grouping function.

What I am asking for help with is a way to transform my current data into a format that is readable (list?) by a Graphing Function.

I am completely open to using third party plugins, pre storing the data locally ETC, I am just unsure how to achieve what I am trying to.

Thanks in advance,

Ella

In your current setup you’re largely working against the grain of the database.
You may well find similar issues reoccur across multiple parts of your app; querying your database horizontally introduces a new type of inefficiency to your flow.

For instance, to build your graph dataset you will actually need to conduct upwards of 5 separate queries on your database - each one of these will be a separate call to and from your datasource, followed by you then merging the data together to create a readable list for the graph / table etc.

So one alternative - a half-way option between what you want to get away from and your current solution - would be the below:

In this structure you maintain aggregated entries for each macro - so, not a separate entry for each macro - and bring in a ‘macro’ field to identify which macro the entry is for.

This will play nicely with charts, tables etc. And bring your searches for labels and values back down to two.

1 Like

Thats great, thank you so much for answering!

So as I understand it, the solution is to have separate entries for each macro, but implement a junction?

IE

Client -> Nutrition plan Junction (a listing of which macro entries belong to which client) -> Macro entries (a listing of macro values)?

I THINK that makes sense! haha

Out of interest, which would be the most technically efficient way of storing my data?

Yes. So your database Thing would probably be called ‘Macro_entry’ or similar - see below. This could have ‘Type’, ‘Dieter’, ‘Value’ fields, and anything else that you need (including belonging to a Plan, yep - but not shown below):

Your ‘Type’ field would probably be linked to another Type, ‘Macro_type’ which just stores information on that macro - ‘Name’, ‘Details’… anything else (or you could use Option Sets for this):

So then your actual database entries would be a list of Macro entries… one of each Macro type for each Dieter… (imagine entries too for Fat, Carbs etc. for ed@wedgehog.io):

So you would end up with Number of Users x Number of Macro types entries in your database under Macro Entries.
You would then run searches for Macro Entries filtered by the current User / Dieter and that will give you what you need for charts / tables etc.

Personally I would probably consider the route that you are looking to get away from. The trade off that you would be making with the route that I’m suggesting above is that you have to maintain up-to-date Sum values under each Macro Type for each User (this applies too to your original structure). There’s an operating cost to this, and a fair few workflows / calls to your database needed to keep the number accurate… plus the possibility of things falling out of sync where an update to the aggregate figure doesn’t happen for some reason.

If you design your site cleverly you can normally hide a lot of the lifting required to run searches on your database and make it feel to the user as though data is readily available when they need it.

But both routes are viable and capable of giving you a snappy user experience. Maybe get the one outlined in more detail above working / tested and take a call on it.

Thank you again for your time, I greatly appreciate it.

My concern is the size of the data base once I then start adding the data fidelity I need.
For example:

A Client is assigned a nutrition plan which contains upto 6 weeks of information (weekly plans), which are then broken down into 7 individual days, each day can have as many as six meals, each which require 4 entries to describe its calories, Protein, fat and carbohydrates assigned to that meal.

This is important as I need to be able to assign different quantities of different macro-nutrients, at different meals (or times of the day), with the amount eaten on each day can vary, and can even vary from week to week. This means that each client may have as many as 1,008 individual Macro_Entries to describe a single training blocks nutrition under the proposed schema.

For context this is for coaching elite athletes nutrition and training.

A hand drawn example of the schema is attached below.

If this truly is the most efficient way to structure my database, I have no issue in doing so, but as this scales up to thousands of users it will quickly break.

Again, I would be greatly appreciative of any advice you ca offer.

Warmly,

Ella