How to handle data model with long text column + associated embeded metadata in an Android Room database

Question

I'm new to Android, and rather new to SQL in general.

I have a data model where I have a Text that consists of TextMetadata as well as a long string, which is the text content itself. So

Text {
  metadata: {
     author: string,
     title: string
     // more associated metadata
  },
  textContent: long string, or potentially array of lines or paragraphs
}

I'd like to load a list of the metadata for all texts on the App's landing page, without incurring the cost of reading all the long strings (or having operations be slowed down because the table has a column with a long string?).

What is the proper pattern here? Should I use two tables, and related them? Or can I use one table/one @Entity, with embedded metadata, and do some fancy stuff in the DAO to just list/sort/operate on the embedded metadata?

Most of my background is with NoSQL databases, so I could be thinking about this entirely wrong. Advice on the general best practices here would be helpful, but I guess I have two core questions:

1. Does having a long/very long string/TEXT column cause performance considerations when operating on that specific table/row?
2. Is there a clean way using Kotlin annotations to express embedded metadata that would make it easy to fetch in the DAO, without having use a long SELECT for each individual column?

Answer 1

This is a good question that is also relevant to other environments.

The Core Issue: How to store large data without effecting your database?

As a rule of thumb you should avoid storing information in your database that is not queryable. Large strings, images, or event metadata which you will never query - does not belong in your db. I was surprised when I realized how many design patterns there are regarding to mongo db (which are relevant to other noSQL databases as well)

So, we know that this data should NOT be stored in the DB. But, because the alternative (file system) is WAY worse than that (unless you would like to implement your own secured file-system-based store) - we should at least try to minimize its footprint.

Our Strategy: save large data chunks in a different table without defining it as an entity (there is no need to wrap it as entity anyway)

How Are We Going To Do That?

Well, thankfully, android room has a direct access to sqLite and it can be used directly (read the docs). This is the place to remind us that android room is built on-top of sqLite - which is (in my own opinion) a fascinating database. I enjoy working with it very much and it's just getting better as the time goes by (personal opinion). Advantages? we are still using android APIs while storing large data in a performant, unified and secure way. yay

Steps we are going to perform:

• Initiate a class which will manage a new database - for storing large data only
• Define a command that will create our table - constructed of 2 columns
  • key (primary key) - the id of the item
  • value - the item itself
• In original db for the Text entity - define a column that will hold the id (key) of the large text stored
• Whenever you save an item to your large items table - get the id and store it in your entity

You can of course use only 1 table for this.. but.. I know that sqLite requires a certain amount of understanding and it is NOT as easy as android room so.. it's your choice whenever to use 1 or 2 tables in your solution

Below is a code that demonstrates the main principal of my proposal

object LargeDataContract {
    // all tables for handling large data will be defined here

    object TextEntry : BaseColumns {
        const val TABLE_NAME = "text_entry"
        const val COLUMN_NAME_KEY = "key"
        const val COLUMN_NAME_VALUE = "value"
    }
}

// in the future - append this "create statement" whenever you add more tables to your database
private const val SQL_CREATE_ENTRIES =
    "CREATE TABLE ${TextEntry.TABLE_NAME} (" +
            "${TextEntry.COLUMN_NAME_KEY} INTEGER PRIMARY KEY," +
            "${TextEntry.COLUMN_NAME_VALUE} TEXT)"

// create a helper that will assist you to initiate your database properly
class LargeDataDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(SQL_CREATE_ENTRIES)
    }
    
    companion object {
        // If you change the database schema, you must increment the database version. Also - please read `sqLite` documentation to better understand versioning ,upgrade and downgrade operations
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "LargeData.db"
    }
}

// create an instance and connect to your database
val dbHelper = LargeDataDbHelper(context)

// write an item to your database
val db = dbHelper.writableDatabase

val values = ContentValues().apply {
    put(TextEntry.COLUMN_NAME_VALUE, "some long value goes here")
}

val key = db?.insert(TextEntry.TABLE_NAME, null, values)

// now take the key variable and store it in you entity. this is the only reference you should need

Bottom Line: This approach will assist you to gain as much performance as possible while using android APIs. Sure thing, not the most "intuitive" solution, but - this is how we gain performance and making great apps as well as educating ourselves and upgrading our knowledge and skillset. Cheers