Best Database design option for Android application with huge data

Question

I am new to Android Application Development and a new member at stackoverflow. I am currently trying to design a recipe application. I have decided upon the features of the app and the scope it will cover. The scope is very vast for me in terms of covering all the recipes from all over the world. I am to deal with a lot of data in this process.

I am currently trying to figure a good and efficient way of handling the data in my app. So far, as per what I have read in different forums, I believe that I have two options in terms of a database choice : 1) SQLite 2) Database on remote server (MySql/Postgre)

Following are some of the thoughts that have been going on in my mind when it comes to taking a decision between the two :

1) SQLite : This could be a good option but would be slow as it would need to access the file system. I could eliminate the slowness by performing DB data fetch tasks in the AsyncTask. But then there could be a limitation of the storage on different phones. Also I believe using SQLite would be easier as compared to using a remote DB.

2) Remote Database : The issue that I can see here is the slowness with multiple DB requests coming at the same time. Can I use threads here in some way to queue multiple requests and handle them one by one ? Is there an efficient way to do this.

Also I have one more question in terms of the formatting of my data once I pull it out from the above DB's. Is there a way I could preserve the formatting of my data ?

I would be more than thankful if someone could share their knowledgeable and expert comments on the above scenario. Also this is not a homework for me and I am not looking for any ready made code solutions. I am just looking for hints/suggestions that would help me clear my thoughts and help me take a decision. I have been looking for this for sometime now but was not able to find concrete information. I hope I will get some good advice here from the experienced people who might have encountered similar situation.

Thanks for reading this long post.

Answer 1

What about combining both approaches?

• A local SQLite database that has the least recently used receipes so you don't need network all the time. Network is way slower than accessing the filesystem.

• Some remote database accessed via some HTTP interface where you can read / write the whole database. And if you want users to be able to add receipes for other users to see you'll need an external database anyways.

SQLite : This could be a good option but would be slow as it would need to access the file system.

Accessing a local database is pretty fast, 5ms or so if it's just a simple read only query on a small database.

But then there could be a limitation of the storage on different phones

Depends on your definition of huge database. It is okay if it is only 2MB which would be enough to store lots of text-only receipes.

Also I believe using SQLite would be easier as compared to using a remote DB.

Yes, Android has a nice built-in SQLite API but no remote database API. And you don't need to setup a database server & interface.

The issue that I can see here is the slowness with multiple DB requests coming at the same time.

A decent database server can handle thousands of requests. Depends on your server hardware & software. https://dba.stackexchange.com/ should have more info on that. Required performance depends on how much users you have / expect.

I'd suggest a simple REST interface to your database since it's pretty lightweight but does not expose your database directly to the web. There are tons of tutorials and books about creating such interfaces to databases. There are even hosted database services like nextDb that do most of the work for you.

Is there a way I could preserve the formatting of my data ?

You could store HTML formatted data in your database and display it in a WebView or a TextView (via Html#fromHtml()) - both can display formatted text.

Databases don't care what type of text you store, for transfer over the internets you may need to encode the text so it does not interfere with the transport formatting (XML, JSON, ...).

Answer 2

A simple way is to integrate Parse into your app. They have a nice framework that easily integrates into iOS and Android. Their plan is freemium, so you'll be able to use up to 1 million API request for no charge, and then its 7 cents for every request after that.

You'll have 1gb to store all your data sets / images, etc.

I don't use parse for everything, but I HIGHLY recommended it for large data schemes because they do all the scaling for you. Check out the API, I think it would be worth your time.

I just started to work on a few of my own projects, and I'm using Parse again. I have to say it's improved a lot over the last 6-8 months. Especially with the Twitter and Facebook integration.