SQLite good practices & ContentProvider

Question

Goal

I would like to ask a couple questions regarding an implementation of a solution for an Android app.

Context

We have a SQlite database for our App. We also use ContentProvider to easily manage changes on the database data and get notified when it happens.

The App has an Activity that contains a ViewPager with 4 Fragment. 2 out of those 4 Fragment use common data.

Issue

Let's say we store credit card data of the user. He may have several cards saved in the database, so we need to know which one is active.

I could only think of 2 solutions for this.

1. Save it on Preferences
2. Add an active field in credit card table

An example of the 2nd solution:

╔════════════════╦═════════════╦═══════════════╦════════╗
║      name      ║     id      ║ secret_number ║ active ║
╠════════════════╬═════════════╬═══════════════╬════════╣
║ personal visa  ║ 02468024680 ║           659 ║ true   ║
║ company visa   ║ 13579135791 ║           362 ║ false  ║
╚════════════════╩═════════════╩═══════════════╩════════╝

I think that the 2nd one is better since the ContentProvider will notify our Fragments when the active card is changed and they will be able to reflect the change in that moment.

Questions

However, I am not sure if this is within good practices since it can lead to problems, such as having multiple rows marked as active. Apart from that, I would also like to ask if there is any way to update all rows with one sql statement, I mean, active one and set the rest to false.

Thanks in advance.

Answer 1

Once I found the way to update all rows at once I am going for it.

I just have to use CASE WHEN ... THEN ... ELSE ... END statement, which I forgot it existed!

Found it here