TransWikia.com

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!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP