A little question about database design


I’m working on an app that has tables similar to Excel where users can dynamically add columns and rows. I have a data type for rows and another for columns. For cell values, I was using a separate data type called “Cell value,” which contained fields specifying which column and row it belonged to and it’s value.

However, when I added an import function to my project, allowing users to add data from .csv files, I encountered a workload issue. While importing a only 500-row file via the Data API wasn’t too costly, but creating 20 cell values for each row meant I had to create 10,000 things (500 x 20), leading to thousands of workload usages during an import.

To solve this, I added cell values as text lists within the row data type. Now, each cell value is stored as “column’s unique id-value,” reducing the workload to just 300wu for 500 rows. It’s working quite well, and I can make desired changes using split, find, and replace when necessary.

The main question is: Will I regret this in the future?.. Could having many users making additions and deletions on a text cause a problem like race condition? This is what I’m curious about, despite the advantages.

Thank you.

Probably not on single texts, on lists yeah if you have lots of users.

@adamhholmes posted a good reply on how to do this somewhere but I can’t find it… someone (or him) can link it here. Your current structure won’t help you.

1 Like

Oh, thank you. I would appreciate it if someone who has experience about this type of database structure could help.

You could potentially use the same approach but have a single text field and input JSON and then split those on the front end etc?