The most common problem that we face when build an e-commerce app is designing and persisting the order and its items data. This article will walk you through the steps to tackle this kind of problem in real production. As an Android developer, we have a very powerful assistant to help us do this, which is Room.
Problem statement
Given that we have to build an e-commerce app, where users can add products to cart, place orders and send them to our server. Sometimes users do not complete the whole order process, they just add items to cart, leave it there and close the app. To maximize user experience in the app, we need to persist the order data so that users can complete later.
Approach
In the general e-commerce business domain, we have the products, each one contains information like name, description, price. The next one is the order, with its line items, address, total price and additional information. Each line item will have basic information about the product along with quantity, subtotal which is calculated from the product price and quantity.
Basically, our order creation process will contain these steps. First, the user selects a product, edits quantity, and adds it to cart. Next, a line item is created with quantity and added to the line items list inside the cart. Remember that the cart here is just the order but has a status property to be distinguished from the completed one.
Another importance is the relationships between database tables. In this case, because each product would be added as a line item, the relationship between product and line item table would be One-to-one. The relationship between order and line line item table would be One-to-many since an order contains a list of line items.
Now we have some useful bullet points to tackle the problem:
- The product contains id, name, price
- The line item contains id, the product’s id, product’s name, quantity, subtotal
- The order contains list of line items, total, address
- The relationship between
product and line itemisOne-to-one (1–1) - The relationship between
order and line itemisOne-to-many (1-n)
Actual code steps by steps
Step 1: Define product entity
| @Entity(tableName = "products") | |
| data class Product( | |
| @PrimaryKey | |
| @NonNull | |
| @ColumnInfo(name = "productId") | |
| val id: String, | |
| @ColumnInfo(name = "name") | |
| var name: String?, | |
| @ColumnInfo(name = "price") | |
| var price: Double | |
| ) |
Step 2: Define the line item entity
| @Entity(tableName = "line_items") | |
| data class LineItem( | |
| @PrimaryKey(autoGenerate = true) | |
| @ColumnInfo(name = "lineItemId") | |
| val id: Long, | |
| @ColumnInfo(name = "productId") | |
| val productId: String, | |
| @ColumnInfo(name = "orderId") | |
| val orderId: String, | |
| @ColumnInfo(name = "quantity") | |
| var quantity: Int, | |
| @ColumnInfo(name = "subtotal") | |
| var subtotal: Double, | |
| ) |
Explanation: The productId here is to reference the product that the current line item is created from. The orderId is to reference the order that this line item belongs to.
Step 3: Define order entity
| @Entity(tableName = "orders") | |
| data class Order( | |
| @PrimaryKey | |
| @ColumnInfo(name = "orderId") | |
| val id: String, | |
| @ColumnInfo(name = "status") | |
| var status: String, | |
| @ColumnInfo(name = "address") | |
| var address: String | |
| ) |
Tips: As you can see, in the product entity, I named the id column name in the Product as productId, the same name with productId in LineItem entity. I did the same thing to the orderId column in LineItem and Order entity. By doing this, it is easier for us to define the relationships between tables since we do not need to know which one owns this column name (see the next step)
Step 4: Define relationship between Product and Line Item
| data class LineItemAndProduct( | |
| @Embedded val lineItem: LineItem?, | |
| @Relation( | |
| parentColumn = "productId", | |
| entityColumn = "productId", | |
| entity = Product::class | |
| ) | |
| val product: Product? | |
| ) |
Jobs
Step 5: Define relationship between Order and Line Item
| data class OrderWithLineItems( | |
| @Embedded var order: Order, | |
| @Relation( | |
| parentColumn = "orderId", | |
| entityColumn = "orderId", | |
| entity = LineItem::class | |
| ) | |
| val lineItemList: MutableList<LineItemAndProduct> | |
| ) |
Here is our DAO with database query to get our data
Line Item DAO
| @Dao | |
| interface LineItemDao { | |
| //Get all line item in along with its product | |
| @Transaction | |
| @Query("SELECT * FROM line_items ") | |
| fun getAll(): Flow<List<LineItemAndProduct>> | |
| //Get line item in an order by orderId | |
| @Transaction | |
| @Query("SELECT * FROM line_items WHERE orderId = :orderId") | |
| fun getLineItemInOrder(orderId: String): Flow<List<LineItemAndProduct>> | |
| } |
Order DAO
| @Dao | |
| interface OrderDao { | |
| //Get an order along with its line items by orderId | |
| @Transaction | |
| @Query("SELECT * FROM orders WHERE orderId = :id") | |
| fun getById(id: String): Flow<OrderWithLineItems> | |
| //Get an order by its status | |
| @Transaction | |
| @Query("SELECT * FROM orders WHERE status = :status LIMIT 1 ") | |
| fun getCartWithLineItems(status: String): Flow<OrderWithLineItems> | |
| } |
Conclusion
Above is an approach to tackle database problems with Room. This is just a basic case with a little code, but it is extendable. When the problems come up with more complicated stuff, just add more columns and relationships between tables.
TL; DR
During our career as an Android Developer, we might face many problems that make us overwhelmed at the beginning. When the problem is too big, just try to break it into smaller pieces and approach with basic knowledge. Try to use the power of the library as much as possible.
