Android Database Design, to use SQLite or just straight mySQL & JSON

| | August 6, 2015

I’m working on an android app which pulls a long list of recipes names into a list view, which the user can filter through and then click on to get more details. The initial recipe list is going to have about 1000 entries and will be stored in an ever changing remote mySQL database. Upon clicking on a single recipe from the list view, the full recipe will be shown.

My question is as follows. Is it better for me to have a background service that pulls the entire list of recipes into a local SQLite database or should I just query the mySQL database every time the list view is pulled up? I’m not too worried about offline use, just optimizing my data structure to allow for a large user base.

One Response to “Android Database Design, to use SQLite or just straight mySQL & JSON”

  1. Why not do the following:

    Have your SQLite database store a date representing the last time data was retrieved from the MySQL web service. Then on your MySQL database make sure you store the recipe add date with each recipe. When your user launches the app, you can construct a query that only gets data newer than the date stored in your SQLite database. This will significantly reduce download times and server stress.

    Since you know the initial recipe list will contain around 1000 entries, you can include that in your APK. This way right from the start queries to the web server will be small. Then over time you might decide to update the local entries in your app in different release versions.

    You could do your transactions with a background service, but I wouldn’t. Your customers might not like unknown processes eating up their battery. For small data transactions you might just throw up a progress dialog or do all this during a splash screen.

    Hope that helps :)

Leave a Reply