CQRS-Style Database Schema: Write Optimized Tables and Read Only Views

CQRS-Style Database Schema Overview

This article explores a CQRS-style schema design for SQL, where write operations use tables and reads rely on views for simplicity. Note: Edited in 2026/1/26.

Introduction

How do you design database tables for complex requirements while keeping the schema simple?

A SQL database is a convenient way to store large amounts of data for later queries. However, there is often a trade-off between making the schema read-friendly or write-friendly because normalization usually requires JOIN queries to obtain the desired data.

Architectural Design Overview

CQRS Pattern

CQRS Separation of Write and Read

Command Query Responsibility Segregation (CQRS) is a design pattern that separates the responsibilities of read and write operations:

• Command:
  Commands can be described as small business tasks that involve updating data. For example, you can add a plant to the garden planting list. They bridge user intentions with data changes.
• Query:
  Queries are operations that retrieve business state from data storage. For example, you can get a flow of plants from the database. These operations do not alter data.

CQRS divides data access into two categories: writes and reads. Designing a single table that performs well for both can be quite challenging.

Database View

Usage of Database View

A database view is a virtual table defined by a JOIN query that runs on one or more tables:

• You can create new queries based on database views without needing to know the schemas of the underlying tables.
• Since a database view can represent a completely different model, it is well-suited for implementing read operations.

The segregation of commands and queries can be achieved by utilizing database views. A denormalized virtual table can be optimized for read operations, making it easier to normalize the real tables.

Implementation Details

Database Tables

@Entity(primaryKeys = [Column.PHOTO_ID])
data class PhotoEntity(
    @ColumnInfo(name = Column.AUTHOR_ID) val authorId: String,
    @ColumnInfo(name = Column.AUTHOR_NAME) val authorName: String,
    @ColumnInfo(name = Column.IMAGE_URL) val imageUrl: String,
    @ColumnInfo(name = Column.PHOTO_ID) val photoId: String,
    @ColumnInfo(name = Column.PLANT_NAME) val plantName: String
)

@Entity(
    primaryKeys = [Column.PHOTO_ID],
    foreignKeys = [ForeignKey(
        childColumns = [Column.PHOTO_ID],
        entity = PhotoEntity::class,
        onDelete = ForeignKey.CASCADE,
        parentColumns = [Column.PHOTO_ID]
    )]
)
data class PhotoRemoteOrderEntity(
    @ColumnInfo(name = Column.PHOTO_ID) val photoId: String,
    @ColumnInfo(name = Column.REMOTE_ORDER) val remoteOrder: Int
)

Get zerg 1111’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in
• Tables should be normalized for write operations. In this case, two tables are separated based on their different data sources.
• The properties of PhotoEntity map directly to API item properties, while PhotoRemoteOrderEntity represents the order of items in the response list.
• If a new feature such as photo editing is required, another table can be specifically designed for it without interfering with existing structures.

Database Views

@DatabaseView(
    "SELECT " +
            "PhotoEntity.${Column.AUTHOR_ID} AS ${Column.AUTHOR_ID}, " +
            "PhotoEntity.${Column.AUTHOR_NAME} AS ${Column.AUTHOR_NAME}, " +
            "PhotoEntity.${Column.IMAGE_URL} AS ${Column.IMAGE_URL}, " +
            "PhotoEntity.${Column.PHOTO_ID} AS ${Column.PHOTO_ID}, " +
            "PhotoEntity.${Column.PLANT_NAME} AS ${Column.PLANT_NAME}, " +
            "PhotoRemoteOrderEntity.${Column.REMOTE_ORDER} AS ${Column.REMOTE_ORDER} " +
            "FROM PhotoEntity " +
            "LEFT JOIN PhotoRemoteOrderEntity ON PhotoRemoteOrderEntity.${Column.PHOTO_ID} = PhotoEntity.${Column.PHOTO_ID}"
)
data class PhotoItemView(
    @ColumnInfo(name = Column.AUTHOR_ID) val authorId: String,
    @ColumnInfo(name = Column.AUTHOR_NAME) val authorName: String,
    @ColumnInfo(name = Column.IMAGE_URL) val imageUrl: String,
    @ColumnInfo(name = Column.PHOTO_ID) val photoId: String,
    @ColumnInfo(name = Column.PLANT_NAME) val plantName: String,
    @ColumnInfo(name = Column.REMOTE_ORDER) val remoteOrder: Int?
)

• To avoid the complexity of queries on normalized tables, we base our read operations on denormalized virtual tables.
• PhotoItemView is a database view defined by a SELECT statement that joins PhotoEntity and PhotoRemoteOrderEntity.
• This lets us query the photo list in the same order as the API response. It makes designing read operations more straightforward, since the database view already contains all the required information.

Data Access Objects

@Dao
interface PhotoDao {
    @Query("DELETE FROM PhotoEntity")
    suspend fun clear()

    @Query("SELECT * FROM PhotoRemoteKeysEntity WHERE :photoId = ${Column.PHOTO_ID}")
    suspend fun getRemoteKeys(photoId: String): PhotoRemoteKeysEntity?

    @Upsert
    suspend fun upsertPhoto(entities: List<PhotoEntity>)

    @Upsert
    suspend fun upsertRemoteKeys(entities: List<PhotoRemoteKeysEntity>)

    @Upsert
    suspend fun upsertRemoteOrder(entities: List<PhotoRemoteOrderEntity>)

    @Transaction
    suspend fun upsertPhotoPage(
        entities: List<PhotoEntity>,
        page: Int,
        pageSize: Int
    ) {
        //...//
    }
}

@Dao
interface PhotoItemDao {
    @Query("SELECT * FROM PhotoItemView WHERE :plantName = ${Column.PLANT_NAME} ORDER BY ${Column.REMOTE_ORDER} ASC")
    fun getEntitiesPagingSource(plantName: String): PagingSource<Int, PhotoItemView>
}

• To comply with CQRS, we create one DAO for the entities and another for the database view.
• DAOs of entities provide access to the commands. Statements here do not need to consider how the information will be retrieved later.
• DAOs of views contain queries to read data, so they don’t need to include how the data is written.

Database

@Database(
    entities = [
        GardenPlantingEntity::class,
        PhotoEntity::class,
        PhotoRemoteKeysEntity::class,
        PhotoRemoteOrderEntity::class,
        PlantEntity::class
    ],
    version = 1,
    views = [
        GardenPlantingItemView::class,
        PhotoItemView::class,
        PlantItemView::class
    ]
)
abstract class SunflowerCloneDatabase : RoomDatabase() {
    abstract fun gardenPlantingDao(): GardenPlantingDao
    abstract fun gardenPlantingItemDao(): GardenPlantingItemDao
    abstract fun photoDao(): PhotoDao
    abstract fun photoItemDao(): PhotoItemDao
    abstract fun plantDao(): PlantDao
    abstract fun plantItemDao(): PlantItemDao
}

• The implementation of the database itself does not change. You only need to remember to include the database view declarations in the @Database annotation.
• One important thing is that you need to handle database migrations if a database view is modified. Room does support auto migrations. For example, to migrate from version 1 to version 2, you can include autoMigrations = [ AutoMigration(from = 1, to = 2) ] in the @Database annotation.

Pros, and Cons

• Pros:
  Denormalized views and normalized tables make designing SQL statements much easier. They are easier to understand because write and read operations are separated, and optimized.
• Cons:
  Creating database views requires extra boilerplate, and adds additional work to database design. Views also need to be migrated as your database evolves.

Conclusion

The inspiration for this pattern came from my experience working with an API that was designed for the web instead of the Android application. This pattern helped me avoid polluting UI data with API mapping logic to keep the code clean. If you’re struggling with complex query requirements, adopting a CQRS-style schema may save you the headaches.

GitHub — Deathhit/SunflowerClone: Android application architecture example composed of MVVM, UDF…

GitHub — Deathhit/TaipeiTour: Android application architecture example composed of MVVM, UDF and…