How to create a chart builder db structure

I'm looking for ideas or suggestions on how you would go about creating like a chart "builder" db structure, I'm planning on adding a report page for the surveys that have points where the report pages can have different charts and the charts that are the same (bar chart) should have different information being represented.

I'm unsure how to go about creating the db structure and what i'll really need to make it work as expected.

I already have the survey db structure ready I'm really adding the charts to it.

This is what I have so far but I feel like my biggest problem is that I don't know how to understand or know which charts can be created based on the survey. The surveys have many types of questions and no limit to the number of sections of questions.

https://ibb.co/SHLxtjS

Any tips or advice would be great, I don't know if this is relevant but I'm planning to use https://vue-chartjs.org/guide/ to display my charts in the frontend.

Thanks

JussiMannisto

1 year ago

Level 50

Maybe I'm misunderstanding what you mean, but you wouldn't typically build separate database structures just for data presentation. Instead, you'd take existing data, aggregate it into the desired format for display, cache the result if necessary, and pass it to the front end. No custom tables needed.

If I needed to remember what kinds of charts each user has configured on their dashboard, I'd just store that information in a JSON column. Chart options can be flexible, and you only need them in the context of the user. I don't see the point of creating a relational structure to represent them in the DB.

eggplantSword

1 year ago

Level 9

@JussiMannisto let me provide some more context. The dashboard isn't per user but instead it's a group of people that are selected by the user who creates the survey, and currently it's by user type or modal (like Brand, or SalePoint). The reason why I went for extra tables is because of how our system works. It's a SaaS and uses a tenancy style structure, currently this change is being requested by our main customer but not everyone is going to use it because it's specific and is going to be created on a separate page from the regular surveys. We didn't want to add changes specific to only one tenant so the idea is to make it able to be used in any survey that has points (regular and special), that way it's more available for everyone.

martinbean

1 year ago

Level 80

@eggplantsword You shouldn’t be modelling your database based on whatever charting library you’re using. If you model your database based on Chart Library A, and then switch to Chart Library B that expects data in a different format, your database schema is now useless.

Model your data like you would normally. You should then use some sort of transformer class to “prepare” data from your database, into the format the charting library is expecting. You could even use an Eloquent API resource class for this:

class FooChartResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            // Format foo for displaying in a chart
        ];
    }
}

You could then return your data from a controller using this chart-specific resource…

public function index()
{
    // Query your database to get the $foos you're interested in...

    return FooChartResource::collection($foos);
}

…as most charting libraries let you specify an endpoint to fetch the data from that is to be displayed.

1 like

willvincent

1 year ago

Level 54

Generally speaking, there are three chart types that are most useful for a range of data points; pie, bar, and line

and subsets of those in some cases (stacked or grouped bars, multi-line, etc)

Store your data in whatever makes the most sense to be able to query against it to run the kind of reporting you need to run later, not how it needs to be to power the chart.

Suppose you have a survey that asks people 5 questions, and also asks their age and gender, then you can group them by age, by gender, or both, and each grouping would be a result set for your chart. you might only show 18-24 female responders on a chart, or you might show all the groupings together each with their own bar or line.. then for each survey question, count responses by group, etc.

But the basic premise remains, collect the data in a clean way that's queryable and use DTO objects to prepare the data for consumption by your chart - if necessary. All the charting libraries, at the end of the day, generally just require a json payload to configure what they display.

1 like

Please or to participate in this conversation.