iOS Database options and app design advice

[roeik]

Hi All,

I am about to start building a database-oriented application for my company. I have experience developing IOS applications, but this my first application using quite big databases and I would appreciate some design advice.

The purpose of my app is to display and market an inventory. The inventory contains between 200-1000 items. Each item has around 20-30 fields. The inventory doesn't change often, but it does change so I will need to update the database on the user's device every few weeks. I can download a csv file from our server, pretty straight forward task, but now I am debating what would be the best way to go about storing the database. I am planning to build a search engine as well, so I want everything to be fast.

The firs thing I need to decide is what database to use. I did some research and I narrowed it down to three options: SQLite, property lists, and NSCoding (quite confused about the last).

Which way do you recommend? Any help with choosing the database, as well as architecture of the app and objects, would be greatly appreciated.

 

[PhoneyDeveloper]

plists and NSCoding are the same, or are at least related. While it sounds like you have a modest amount of data I would use sqlite for a project like this. It's more scalable and will probably use less memory and be faster than the plist strategy. Core Data is the other option. I don't use it at all so I wouldn't use it for this project. It's complicated to learn.

Is your data completely static? That is does it ever change on the device?

I would ship a pre-built database with the app and then download the updated database sqlite file from a server periodically. Write the downloaded db to your app sandbox, either in the Documents folder or the Application Support folder, or perhaps Caches. There's some issue with backups to iCloud that you have to deal with in this case.

It should be possible to build a database using sqlite on a Mac or even other OSes that will be read on the device. You can script the sqlite command line tool to build or edit a sqlite db for your updates.

 

[robvas]

We store our items in CoreData. Then every time the app starts, the server is queried for the changed objects since the last time it was updated.

So if you updated last on Monday, the server will return any object that has been modified (or created) since Monday. That way you're only refreshing a few items.

The server side is simple a Ruby program (that's what or web app is written in) that simply returns the information in JSON format. Of course, the phone has a key and authentication setup on it.

 

[roeik]

Thank you for your reply.

I think sqlite will be the best way to go for me.

Right now our inventory management system exports the inventory to a csv that I am able to download from the web, so I will need to import everything from the csv after downloading it to the device. I don't have the time and resources to manually create an sqlite database every week. Is it even possible to create sqlite database on first lunch and then import to it everything from the csv that I download?

----------

I forgot to answer, yes the data is static. Once it's on the device it won't change.

 

[PhoneyDeveloper]

There's plenty of flexibility here. If you want to download a csv file and merge that into the db or create a new db from scratch on the device that can work. If you want to create a sqlite db on your Mac from the csv and then have the devices download that and then replace their old db you can do that too.

If everything is static I would most likely build the db and download that from the server. I think it will be pretty much the same code either on the device or on the server to generate the sqlite db. Although you might prefer to use the sqlite command line tool to script building the db.

You might want to compare the relative sizes of the csv file and the sqlite db. Also determine how long it takes to build the db on the device. If only a few seconds then do what is easiest. If it takes a minute then download the db.

BTW, I recommend that you use the FMDB Objective-C wrapper to sqlite rather than straight-C sqlite calls.

 

[roeik]

What's the difference then between FMDB and CoreData? Aren't both of them are sqlite wrappers?

 

[PhoneyDeveloper]

FMDB is a fairly thin wrapper over sqlite. It changes the API to be in Objective-C but you still pass in SQL statements and get back result sets.

Core Data is completely different. It's an object store that happens to be backed by sqlite. In fact you can back it with other data storage mechanisms like a plist, but no one ever does that.

If you know SQL you'll easily learn how to use FMDB. Core Data is more difficult to learn and there's no SQL involved.

 

[larswik]

I too am starting to learn Core Data. I have been using Plists and love them for simple things. I have a project coming up so I got this book by Apress "Pro Core Data for iOS".

I have the book on my iPad and keep it but reading chair in the bathroom I just finished Chapter 2 and it seems good so far.

Worth checking out I would say.

 

[seepel]

I see core data as being an object oriented wrapper around SQLite.The main advantage of using core data as IEEE it is that toucan tightly integrate it into a TableView fairly painlessly. I net you could save a fair amount of bandwidth by transferring updates as binary plists, which should be fairly sile translate into the store.This is the route I am taking with a current project of mine. I'd really only go with SQLite if you anticipate doing a large number of batch interactions, as this is where core data can get bogged down.