Android: Dealing with SQLite DBs in my program

Question

I am making a pokemon like game that will utilize SQLite DBs for storage and I'm trying to figure out how to best set it up.

I would like to have three tables:

• One for all the "pokemon" and their stats
• One for all the "pokemon" moves
• One for the player's "pokemon"

I was thinking of having the app download the DB from my website so that I can add new "pokemon" and update/balance stats, but I want the player's "pokemon" list to stay the same unless they add/remove them from their teams.

Should I set up a DB with 2 tables for the "pokemon" and moves, and a second DB just for the player's "pokemon"? And how should I manage downloading/storing the DB? Currently I have it set up to download the sqlite DB to the SD card then have the app create a new SQLite DB passing the file location. What could I do to update the DB after I've made changes?

• ByteMe: have you looked into ormLite at all? it can be a useful way to keep track of accessing multiple tables in a database from different activities.

Answer 1

My advice is to change the way the database is being updated.

Instead of downloading the whole database as a file, as that seems to be what you're doing, a more suitable solution could be to have the application look for updates through a webservice and, if new updates are available, return them as XML / JSON / etc.

Your application could then parse the received data to generate SQL statements that would update the database without affecting current player stats.

• willmer: By webservice, would it be something I would have to create on my website to handle receiving input from the app and sending the new DB info to the app?
• Telmo Marques: @willmer yes, like a PHP script or application that would return the updates in a strutured way. For instance, your Android application would send the current version to a webservice located at yoursite.com/update.php and, if new updates were available, it would return them as JSON or XML, for example. Having two sepparate DB's could also work as a solution, but it comes down to how much control you want to have on the update mechanism.
• willmer: Thanks for your answer. I was hoping to keep it simple for now to get the game working and then look to improve it. I'll have a lot of artwork to do so while that is progressing I can investigate the use of JSON or XML to update my DBs.
• Squonk: @willmer: It could be as simple as a simple HTTP GET for a latest-version.txt file which contains a line such as 20120303 (that being a date string). The app would then download the file latest-pokemon-20120303.xml (for example). It's a simple way to start and wouldn't require any server-side scripts just a manual update of the xml file and the 'latest version' file.
• willmer: @MisterSquonk Sorry to ask more questions. Would that latest-version.txt file be in the app so that when I update the app it would see that change in the txt file and then downloaded the latest xml file?