Data modeling for a Spreadsheets to Charts application

Database Administrators Asked by Ivor on August 23, 2020

I’m working on an app that imports spreadsheets from Google Sheets API and I’ve been struggling for days with an efficient way to model the data.

As you know PostgreSQL has JSONB which is efficient for this and easy to work with but, the problem I’m facing is that a spreadsheet can have any type of data and columns here are some of my requirements:

A spreadsheet Table to store all the spreadsheets information including sheets/tabs and all the columns and rows. I decided to add a data:jsonb column to store the data, of course this is very inefficient in a spreadsheet with a lot of data but I don’t know how to optimize it. Along with the data goes the metadata info (title, id, etc)

A metrics Table to store information about charts and diagrams to build any type of charts (bar, pie, etc). What I need is to store a small chunk of the data column from the spreadsheets table, but I solved this with a nice library called HandsOnTable that builds a spreadsheet in a React component using a JSON object, so I can just grab the data from spreadsheets, dump it on the library, select the data range in the UI and make a POST request to add a chart record as jsonb too in the metrics table with the selected data.

My question is, is this the only way to do it efficiently?

Add your own answers!

Related Questions

SqlPackage does not pick up variables from profile

3  Asked on December 24, 2021 by thx-1138


This simple mysql query is taking 2-3 seconds

2  Asked on December 24, 2021 by mikkergp


Powershell Invoke-Sqlcmd Replacing £ with?

1  Asked on December 24, 2021 by dennis-finke


Upgrade Postgres 9.6 to 12

0  Asked on December 24, 2021 by doug-p


Stored procedure for adding cascade

1  Asked on December 22, 2021 by user73639


MySQL Optimization Guidance Needed

3  Asked on December 22, 2021 by enyceexdanny


Sybase IQ cache database result

1  Asked on December 21, 2021 by daria


Help Outputing MySQL General Log to Named Pipe

2  Asked on December 21, 2021 by widdlewabbit


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP