Day —5 : How to Pick the Right Database Before It’s Too Late

Choosing the wrong database at the start of a project is like building a house on the wrong foundation — you won’t feel the problem until it’s too expensive to fix.

The Problem Every Android Dev Faces

You start a new project. You open Android Studio. You think “should I use Room or Firestore?” You pick one based on what you used last time, or what a YouTube tutorial used, or just a gut feeling.

Three months later your app is slow, your queries are a mess, or you’re paying a Firestore bill that keeps growing because you picked the wrong tool for the job.

This article gives you a clear, simple rule for when to use each one — and why.

The Simple Way to Think About It

Imagine two types of storage:

SQL — The Spreadsheet

SQL databases store data in tables. Think of a spreadsheet with rows and columns. Every row follows the exact same structure.

The superpower of SQL is relationships. You can link tables together. Users table + Posts table + Likes table — and you can ask complex questions like “give me all posts liked by users who signed up this month.”

When to use SQL (Room) on Android:

• Your data has clear relationships (users → posts → comments)
• You need complex filtering and sorting
• Data must be consistent — no duplicates, no orphans
• You’re building offline-first (Room is built for this)

The Decision Table — Use This Every Time

Android Implementation

// ── ROOM (SQL) — for structured, relational, offline data ──────────────────

@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey val id: String,
    val name: String,
    val email: String,
    val createdAt: Long = System.currentTimeMillis()
)

@Entity(
    tableName = "posts",
    foreignKeys = [ForeignKey(
        entity = UserEntity::class,
        parentColumns = ["id"],
        childColumns = ["authorId"],
        onDelete = ForeignKey.CASCADE  // delete user = delete all their posts
    )],
    indices = [Index("authorId")]      // always index foreign keys!
)
data class PostEntity(
    @PrimaryKey val id: String,
    val authorId: String,             // links to UserEntity.id
    val title: String,
    val content: String
)

@Dao
interface PostDao {
    // JOIN query — SQL's superpower
    @Query("""
        SELECT posts.* FROM posts
        INNER JOIN users ON posts.authorId = users.id
        WHERE users.email = :email
        ORDER BY posts.createdAt DESC
    """)
    fun getPostsByUserEmail(email: String): Flow<List<PostEntity>>
}

// ── FIRESTORE (NoSQL) — for flexible, real-time, nested data ───────────────

data class UserProfile(
    val name: String = "",
    val role: String = "",
    val skills: List<String> = emptyList()
)

class UserRepository {
    private val db = Firebase.firestore

    // Real-time listener — updates pushed to app automatically
    fun getUserProfile(userId: String): Flow<UserProfile> = callbackFlow {
        val listener = db.collection("users")
            .document(userId)
            .addSnapshotListener { snapshot, error ->
                if (error != null) { close(error); return@addSnapshotListener }
                val profile = snapshot?.toObject(UserProfile::class.java)
                if (profile != null) trySend(profile)
            }
        awaitClose { listener.remove() }
    }

    // Write nested data — no schema needed
    suspend fun updateProfile(userId: String, profile: UserProfile) {
        db.collection("users").document(userId)
            .set(profile)
            .await()
    }
}

Common Mistakes Android Developers Make

Mistake 1 — Using Firestore for everything because it’s easier to start

// ❌ Querying Firestore like a relational DB — slow and expensive
db.collection("posts")
    .whereEqualTo("authorId", userId)
    .whereEqualTo("category", "tech")
    .orderBy("likes")
    .get()  // Firestore charges per document read — complex queries = high bill

// ✅ This kind of query belongs in Room (SQL) — free and fast
dao.getPostsByAuthorAndCategory(userId, "tech")

Mistake 2 — Using Room without indexes on foreign keys

// ❌ No index — every query scans the entire posts table
@Entity(tableName = "posts")
data class PostEntity(val authorId: String, ...)

// ✅ Index on foreign key — queries run in milliseconds
@Entity(tableName = "posts", indices = [Index("authorId")])
data class PostEntity(val authorId: String, ...)

Mistake 3 — Forgetting Room migration when schema changes

// ❌ Bump database version without migration = crash on update
@Database(entities = [UserEntity::class], version = 2)

// ✅ Always add a migration
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE users ADD COLUMN bio TEXT DEFAULT ''")
    }
}

Production-Ready Example — Hybrid Approach

Most real apps use both. SQL for structured local data + NoSQL for real-time features:

// Best of both worlds — Room for offline, Firestore for real-time sync
class PostRepository @Inject constructor(
    private val dao: PostDao,          // Room — local, offline, fast queries
    private val firestore: FirebaseFirestore  // Firestore — real-time sync
) {
    // Read from Room — always fast, always offline-safe
    val posts: Flow<List<PostEntity>> = dao.getAllPosts()

    init {
        // Sync Firestore changes into Room in the background
        firestore.collection("posts")
            .addSnapshotListener { snapshot, _ ->
                snapshot?.documentChanges?.forEach { change ->
                    CoroutineScope(Dispatchers.IO).launch {
                        when (change.type) {
                            ADDED, MODIFIED -> dao.upsert(change.document.toPostEntity())
                            REMOVED -> dao.deleteById(change.document.id)
                        }
                    }
                }
            }
    }
}

Key Takeaways

• SQL = spreadsheet. NoSQL = folder of documents. Pick based on your data shape, not what’s easier to set up.
• Use Room (SQL) when data is connected, needs complex queries, or must work offline perfectly.
• Use Firestore (NoSQL) when data is simple, nested, or needs real-time sync across devices.
• Always index foreign keys in Room — missing indexes turn fast queries into slow table scans.
• Most production apps use both — Room for local offline cache, Firestore for real-time sync.

AndroidDevelopment #SystemDesign #Kotlin #MobileDevelopment #SoftwareEngineering