SQLite Forum

General question concerning database stucture and number of databases to use.
Login
Thank you for taking the time to write this response. I greatly appreciate it.

I think I have the data set up in a fairly logical manner at this point and have been focusing on efficiency, but I have much to consider after reading your advice and warnings. The application works now as a web extension utilizing indexedDB for all the data apart from the media files.  I was surprised by how quickly the database requests are processed.  The UI is never updated until it is known that the database transaction has succeeded; and I was concerned that it would provide a clunky user experience but it has been very quick.

To build their study moudles, users add pages to tabs (custom tabs in a single web page, not browser tabs, and only one page is loaded at any time in a tab), and add UI components to their pages.  

Most of the data fields are stored in mapping objects that include items such as sequence arrays, undo-chain "pointers", state data, and "pointers" to other data elements to restore the pages and components.  Portfolios, modules, tabs, pages, and some page components each have a mapping object.   

Mapping objects comprise the greatest number of data elements but comprise the smallest amount of data overall. Apart from adding and organizing pages and components which are captured in the mapping objects, the only data users really input are text and media files.  

Everything that needs to be known about the text and media files are held in the mapping objects, apart from the actual text strings and media blobs themselves. Each media blob is a separate row and text strings are stored in sets by component category, one set per row.

In indexedDB, I had object stores set up by tab, and text rows mixed in with mapping-object rows. That now seems to be a poor approach; and, perhaps, mapping objects should be a table, text data another, and media blobs a third.

IndexedDB returns sets of data ordered by primary key; so, sorts are performed in the JavaScript to order the data by sequence array.  Perhaps, that can now be handled by the SQL queries directly such that ordered data can be passed to the JS. However, most operations on the data are easily performed in JS when they act on a single page within a tab, such as validating the user input on a page. This is because all data for the curretly loaded page is held in RAM.  However, to validate all pages in a tab or an entire module through one user click, required opening an indexedDB cursor and stepping through each row of data in turn and validating relevant values. I think this would be the only time it would be easier to use the data in C and have it in tables in real individual columns rather than in a single JSON string.  This became easier when the text and media were completely separated from the mapping objects because only the maps need to be reviewed for validation.  But, as you warned, there may be other operations desired in the future that would be much easier to perform in SQL / C had the data been loaded in real columns rather than JSON strings.

At first, I thought it was going to be an advantage to store the data in columns until I considered the type and frequencey of database requests the application requires.  There seems to be a balance between making edits to pages and building the next desired page. When page component data is stored in a larger number of specific rows, edits are simpler because less data is extracted and updated for each edit request.  However, to build a new page, more rows of data must be extracted and assembled. Also, it appears that, when the full JSON string is held in RAM, it is less work to replace the full string than to update a portion of it.  Of course, even if I am understanding this correctly, the differences in SQLite performance between them may be insignificant to the user experience.  

That isn't a design issue, for the design is primarily the mapping objects, but it still needs to be decided, for example, whether every text container should be a row or all text containers within the same page component should be in the same row. 

Those are the kinds of things I was thinking about when using indexedDB, after I had a data structure in place. Of course, I changed that structure about three or four times along the way as I learned more and have much more yet to learn.

Thanks again for all the advice and warnings.