Database design on IOS - flatten or normalised?

Posted on 2012-08-30
Last Modified: 2012-09-03
Hi, following on from my earlier question:
 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

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
Question by:PatrickK_W
    LVL 14

    Accepted Solution

    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:

    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.
    LVL 1

    Author Comment

    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.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Ever wanted to watch videos in your computer from the bed without the need of standing up and shutting down the computer?  Is your computers operating system Windows XP, Windows Vista or Windows 7?  Do you own a iOS device? If all the answers were y…
    What's a UDID? If you're involved in developing, testing, or even reviewing an iOS application that's in beta, then at some point you may need to know the UDID for any iOS devices that you'll be testing on. What's the UDID? It stands for Unique Dev…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now