Best practice to store large data in SQLite Plugin on Hybrid Apps (one table per blob?)

I am using this SQLite Plugin to benefit from 'unlimited' storage limits as a mobile application. Basically I have base64 imagedata and need to store this in a database.

In addition, I have some other tables and one Masterdata where the primary keys are stored along with some additional information, such as albumName.

Relational setup with Foreign Keys

To cope with the relational aspect of databases, I initially used Foreign Key declarations and created the tables as follows (for simplicity only two tables shown):

      // Master Data
      $cordovaSQLite.execute(db, 
      "CREATE TABLE IF NOT EXISTS \
        Masterdata(\
          id TEXT primary key, \
          albumName TEXT, \
          favorite INTEGER\
        )"
      )

      // Images
      $cordovaSQLite.execute(db, 
      "CREATE TABLE IF NOT EXISTS \
        ImagesData(\
          id TEXT, \
          data BLOB, \
          FOREIGN KEY(id) REFERENCES Masterdata(id)\
        )"
      )

Then, when I wanted to load only the images of a specific album, I run the query:

SELECT Masterdata.id, ImagesData.data FROM ImagesData, Masterdata WHERE Masterdata.album = (?) ["Some album name"]

This worked fine (everything loads as it should), however I noticed immediately that it gets Slow when dealing with a large dataset. This makes sense as base64 strings are quite storage extensive.

Store Blobs in Separate Tables

So I read this post, which recommends to consider storing each BLOB in a separate table, if your data includes BLOBs.

I coded this as follows:

// tableName in the form of 'id'_imagesData
// 'id' is stored also in Masterdata

$cordovaSQLite.execute(db, 
      "CREATE TABLE IF NOT EXISTS "
        + tableName + "(id integer, data BLOB)"
); 

and then I insert the data in this table with the query:

"INSERT INTO " + tableName + " (id, data) VALUES (?,?)", parameters
// parameters = [0, base64_imageData_Str]

Outcome

The outcome is that I noticed a significant increase in the loading time, even when having stored multiple images (thus having multiple tables). It looks to me that it makes sense as we can use the Masterdata to filter out the selected album id's and then only load the images from one table.

My questions are:

• Is this a bad, oke, or good practice?


• Are there disadvantages of having a large number of tables (for example, if I have 1000 images, I will have 1000 tables with this setup)


• If it is bad practice, what is the better recommended way?