• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 834
  • Last Modified:

Database design on IOS - flatten or normalised?

Hi, following on from my earlier question:
http://www.experts-exchange.com/Apple/Programming/iOS/Q_27847640.html
 but on a different topic:

I am looking at writing an IOS app that gets data from our in house system and allows the user to download the list of jobs for the day, add completion details, etc and upload the details back to the live server.

Of course that’s the simple outline – there are a lot of tables and data required to do this. the job itself is made up of multiple tables, and there are other entities, such as parts used, time spent, etc that is collected for the job.
The data structure is quite complicated. With SQLLite as the local DB on the IOS device, is it best to replicate the DB schema in SQLLite or “flatten” the structure as much as possible for SQLLite.

I’ll still need a fair few tables – the main job, parts, timonjob, etc… but should I flatten the data from the related tables for the job into a single table? I wont be updating the job itself just viewing the details so instead of having say 4 (there are more in reality!) tables
Call
Call_priority
Call_status
Address

Should I create a different call table structure on the IOS device which has the relevant fields from the real call table structure, plus the lookup values from the other 3 tables?

Also what is the best way to get data from and to SQLLite from SQLSever? I was looking at doing it via webservices. Are there any better methods? I find it quite hard to find specific stuff abot best practices for data, etc on IOS…. The books on amazon tend to be introductions, then either game or interface heavy with little about data
0
PatrickK_W
Asked:
PatrickK_W
1 Solution
 
Hamidreza VakilianSenior iOS DeveloperCommented:
If your tables are simple enough that you can "flatten" them into a new structure on device the you do so; But usually replicating schemas on client-side will make the synchronization algorithm more convenient to implement.

You may check out these links to get more information on offline database synchronization:
http://developer.appcelerator.com/question/131168/sync-local-and-server-sql-database---any-tools-out-there-or-best-practices
http://blogs.usask.ca/the_bolt/archive/2011/01/titanium_tutorial_mobile_database_sync.html
http://discuss.joelonsoftware.com/default.asp?design.4.605521.13
http://stackoverflow.com/questions/1636274/offline-database-synchronization-with-server

The web services are an easy to implement technology and you will find it simpler to integrate into your synchronization engine; But they are not always the best solution; sometimes creating a socket connection to transfer data will be much more appropriate for a project but harder to implement; hence there's a trade-off.
Of course if you are looking for a resource to guide you through how to make such a project as yours, you probably won't be lucky to find one. because your project is a combination of projects. Instead you would better look for a tutorial/book for each sub-project of your project (e.g. consuming web services in iOS, How to parse XML in iOS, converting a SQLite DB into XML; etc.) and then putting them all together.
0
 
PatrickK_WAuthor Commented:
Thanks, if it was SQL ->SQL or SQL to a .net device, it'd be a breeze, or if IOS had native support from webservices it'd be a breeze.

Although it's not a technically complicated idea its's just identifying the "way apple want you to do it" for IOS.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now