Tips on Mobile Development Database

Whether you're using NoSQL databases like Realm or Firebase, shipped-in-the-box SQLite, or modern SQLite shipped with your app, there are a few general requirements you should always keep in mind:

1. The developers will test DB with 10 records, whereas a user will have a billion records. For performance reasons, we should keep in mind that:

   • All interactions with DB should be in a separate thread, but we should eliminate callback hell (Rx can be our best friend here)
   • Don’t load data that users won't be able to see from database. Usually, users will not look at all of the projects in the list. They'll most likely only be interested in the last 100 of them.
   • Don’t forget to index fields you’ll need to select from. This will make a huge performance difference.

2. Work agile. We can take advantage of things like refactoring stability and compile time checks with statically typed languages. I strongly believe we should use them for all of our logics. There should be a business logic for getting data from local storage in Java Strings.

To explain more, let's compare two different DB definitions:

@Table
class Publisher {
  @PrimaryKey
  public long id;

  @Column
  public String name;
}

@Table
class Book {

    @PrimaryKey
    public long id;

    @column
    public String title;

    @Column
    public Publisher publisher;
}

and string representation:

db.execSQL(“
CREATE TABLE `Publisher` (
  `id` INTEGER PRIMARY KEY,
  `name` TEXT NOT NULL
)
CREATE TABLE `Book` (
  `id` INTEGER PRIMARY KEY,
  `title` TEXT NOT NULL,
  `publisher` INTEGER NOT NULL
    REFERENCES `Publisher`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
“)’

Modern libraries can not only generate raw SQL strings, which will work in the same way most of the time, but also migration schemes. These strings will always stay true, even when we change applications. The whole point is that we don’t what to have to take care of it manually — it will not compile if we try to order the strings by field, which would no longer exist.

Of course, sometimes for very complex requests, we may get a real advantage by using manual written requests. But most of the time, generated requests are not the reasons why applications seem slow (if we're talking about small projects with just a few developers for each platform).

Sure, it might be good to know some SQL language, just like how it'd be nice to know some HTML, probability theory, physics, economics, Bahasa Indonesian, and other things, that you may, hypothetically, need 😁.

With that said, I really don’t understand why any job interviewer would prioritize questions about SQL before questions related to what I've actually done in my career – my ability to create animations, profile performance problems, construct app in a matter of independent layers, and test all translations fit text holders to look nice for all languages. I think you get the point I'm trying to make...

Happy coding!