To SQL or not to SQL, that is my question

Discussion in 'Mac Programming' started by BadWolf13, Aug 22, 2010.

  1. BadWolf13 macrumors 6502

    Joined:
    Dec 17, 2009
    #1
    Ok, so I'm starting a new program, and at the core, this program is basically a database. Now being new to the programming world, I've heard of this SQL thing, but I don't know too much about it. Now the question really to me is, am I wayyyyy better off using SQL for the storage of the information, or is it fine to just use the NSKeyedArchiver protocols to unarchive the information when the program starts, and save it when the program quits?
     
  2. spiffers Suspended

    Joined:
    Apr 12, 2009
  3. xStep macrumors 68000

    Joined:
    Jan 28, 2003
    Location:
    Less lost in L.A.
    #3
    NSKeyedArchiver is not the tool to use. Likely neither is XML or Plists. The problem with all three is that using them requires reading the entire contents of the file. As the file gets larger, it gets slower. Ditto for writing back to it. If you change just one item, you have to write the entire content of the file back.

    Core Data is commonly used in the OS X and now iOS world because once you learn it, you can shorten your development time considerably. It has a lot of built in smarts but it takes time to learn it. I'm just beginning on this path now.

    You also have the option of using SQLite directly. It is already included with OS X and iOS and the dev tools. This might be easier up front to learn compared to Core Data, but your responsible for more of the details of course.

    Finally, you could use MySQ, PostSQL, and perhaps some other options. In this case you have to take care of even more, like finding and including the appropriate libraries. If you app is a personal app where only one person is allowed to use the database at any one time, then I would go with Core Data or SQLite.
     
  4. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #4
    Well the way I'm looking at the program now is that it will load the entire file when the program starts up, and then saves the file when the program closes, so the size of the database would only affect startup and closing.

    I should have mentioned earlier, the first version of this program will be a personal app, but there is a possibility of a networked version to be made down the road.
     
  5. xStep macrumors 68000

    Joined:
    Jan 28, 2003
    Location:
    Less lost in L.A.
    #5
    You don't want more than one person accessing a networked source. That is asking for corruption. In that case, I'd look into a multi user database such as MySQL.
     
  6. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #6
    Now correct me if I'm wrong, but MySQL isn't public, which means I've had to get licensing rights from Oracle to use it in my program, right? Is there a public licensed version of SQL that would also be appropriate for multi-user access?
     
  7. GorillaPaws macrumors 6502a

    GorillaPaws

    Joined:
    Oct 26, 2003
    Location:
    Richmond, VA
    #7
    PostgreSQL has a Liberal license similar to the MIT and BSD. There's a toolkit for Cocoa called PostgreSQL for Mac. Included in the package is PGSQLKIT which is an Objective-C framework for interfacing with the database.

    Honestly though, if you're just getting your feet wet with databases, you might want to start off with Core Data just to get the hang of things. At least make some test projects with it.
     
  8. mdatwood macrumors 6502a

    Joined:
    Mar 14, 2010
    Location:
    Denver, CO
    #8
    What exactly are you trying to do?

    If you're just saving local app settings then a key/value file is fine.

    If you're saving local app data then a key/value file still might be fine.

    If you're going to be saving a lot of local data then a CoreData with SQLite underneath is something research.

    If you need networked shared data then you're moving towards a multiuser RDBMs system. In this case an entire new set of questions come up.
     
  9. BigJimSlade macrumors member

    Joined:
    Dec 16, 2005
    #9
    You are indeed wrong. ;) MySQL is multi-license, one of which is the GPL.

    As GP says, don't worry about it too much for now - get your head around Core Data or SQLite first.
     
  10. knightlie macrumors 6502a

    Joined:
    Feb 18, 2008
    #10
  11. crackpip macrumors regular

    Joined:
    Jul 23, 2002
    #11
    Since you are new to the programming world, there are a few things you need to consider:

    1) Most importantly, your focus should be on the 1.0 version. It's good to have vision on where the app is going in the future, but without v1.0 there is no future. Don't "future proof" your code, especially since your coding skills will improve rapidly as you develop the app.

    2) "Premature optimization is the root of all evil" --Donald Knuth
    You won't know where the bottlenecks of your program will be until you write it. As you are learning, don't spend time outside of Apple's recommended API's until you are sure that it will benefit you. If your optimizations make the code more difficult to understand, verify that the benefits are worth it.

    3) What do you expect users to do with the 1.0 release?
    CoreData is fast for accessing and writing data, but it's not good at querying large datasets (see Brent Simmons blog post about switching from CoreData to SQL in NetNewsWire if your interested). If you envision thousands of database entries with your v1.0 app, then some version of SQL is the better choice. Since you mentioned loading the entire file at start-up, this is not likely the case for your app especially at v1.0.

    So based on what you've written, I'd say use CoreData.

    crackpip
     
  12. GorillaPaws macrumors 6502a

    GorillaPaws

    Joined:
    Oct 26, 2003
    Location:
    Richmond, VA
    #12
    The Brent Simmons blog post you're referencing is talking about Core Data on iOS I believe, not for OSX. We're in agreement though that Core Data is probably the best way to go for this person in this situation.

    The OP might want to read up on designing databases and database normalization. While you don't have to have a perfectly normalized database in Core Data, understanding the theory will make your designs better.
     
  13. Winni macrumors 68030

    Winni

    Joined:
    Oct 15, 2008
    Location:
    Germany.
    #13
    MySQL is dual licensed: Under the GPL - which is as much a "public" license as it gets - or under a proprietary/commercial license.

    So you either purchase a commercial license of MySQL that allows for redistribution with a closed source application -- OR -- you put your own program under the GPL (read: make it Open Source) and bundle it with the community edition of MySQL.

    If you only need a single user local storage, you might want to go with SQLite instead, which is in the Public Domain and thus can be fully integrated in and distributed with your own work without any obligations. Apple uses it in Aperture, for example.

    In a networked environment, if you do not want to purchase a commercial license of anything or want to be forced to license your own work under the terms of the GPL, your best bet will be PostgreSQL. There are others, but PostgreSQL is the most powerful and most mature choice.
     
  14. ranguvar macrumors 6502

    Joined:
    Sep 18, 2009
    #14
    Use Core Data. It's not that difficult to learn and extremely powerful.
     
  15. Eraserhead macrumors G4

    Eraserhead

    Joined:
    Nov 3, 2005
    Location:
    UK
    #15
    if you want multiple users - or to offer multi-platform support avoid Core Data.

    If you want something easy and that isn't cross platform and that doesn't teach you any cross platform skills use Core Data.

    FWIW Postgres is supposed to be good.
     
  16. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #16
    Any particular sources you'd recommend on the subject of database normalization? Also, could you define the term for me?
     
  17. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #17
    Users of version 1.0 are unlikely to have thousands of entries, but could easily reach a hundred. Would Core Data still be optimal?
     
  18. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #18
    Do you want to write the conversion utility so users can move from v1 to v2? If you think a DB is the way to go, just get started that way. If you don't know CoreData well already, no loss.

    -Lee
     
  19. GorillaPaws macrumors 6502a

    GorillaPaws

    Joined:
    Oct 26, 2003
    Location:
    Richmond, VA
    #19
    I bought a book called "Beginning Database Design" that did a good job explaining the basics, but it really didn't get into how database design will ultimately effect performance which was pretty disappointing. Overall the book was a bit juvenile and wasn't necessarily written for a programmer audience.

    Here's the Wikipedia entry on normalization, but you can probably google around to find a decent tutorial.

    I found "Beginning Databases with PostgreSQL: From Novice to Professional" particularly helpful with regards to learning PostgreSQL and databases in general.

    While I realize lee1210's point about not wanting to write an app for core data, and then migrate the data into a full-blown database down-the-line, I think Core Data is a nice introduction to database concepts without having to write SQL statements (not that they're all that hard). Core Data should chew right through hundreds of records very quickly. I would suggest implementing a small test version of your app in Core Data just to get a feel for it, and then take what you've learned and apply it to something like PostgreSQL if you're pretty sure you're going to need multiple people accessing the data down the road. If that's not a feature that you'll need, then Core Data will be easier and faster to work with.

    Lastly, I should mention that there's a framework called baseten that allows you to create a core-data like model but have it actually interface with PostgreSQL. It's not free for commercial apps, but it's only a couple hundred bucks to license.
     
  20. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #20
    Thanks guys, I think I've got a good idea where to go now. Since there's going to be separate versions, the standalone, and the networked version of my software, I think I'll write the standalone with Core Data and learn the SQL stuff before I create the networked version. Sound good?
     
  21. GorillaPaws macrumors 6502a

    GorillaPaws

    Joined:
    Oct 26, 2003
    Location:
    Richmond, VA
    #21
    I would write a scaled-down test-app with Core Data that's not feature complete, or polished. I'd do this just to flesh out how things will work and never release it. Then I'd take what I learned from that project and start with a clean slate and write my app from scratch using a SQL backend that will have a well-structured, clean and organized codebase upon-which to move forward.

    In theory, if you're VERY careful about adhering to MVC, you should be able to plug in different data-stores without too much trouble, but in practice I suspect that's going to be tough. Also don't underestimate how difficult multi-user access is. It's a tricky problem that involves a lot of locking and data-integrity issues.
     
  22. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #22
    The first tutorial I ever did was a basic MVC, and I've kinda stuck with that structure through my own programs, so I think I'm good in that regard. As to the complexity of multi-user access, I probably do underestimate it. That's why I'm writing the standalone version first, which will provide a base for adding in all the network stuff.

    Tell me, is it still standard to have client and server versions of software running, where the server software will run the database and the clients will read and write to it?
     
  23. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #23
    Hello everyone, I'm bringing up this old topic. Since I last posted, I've done a number of Core Data apps, including a mock-up version of what my networked app will be like. Now at this point, I've reached a bit of a dead end, because I'm not sure where to go for network programming in XCode.

    In doing some searching, I came across this old thread stating that Core Data is useless across a network. Has this changed since then? Is Core Data the way to go for a multi-user access database in Cocoa?

    Can anyone give me any pointers on where to go from here?
     
  24. xStep macrumors 68000

    Joined:
    Jan 28, 2003
    Location:
    Less lost in L.A.
    #24
    As far as I know, Core Data is designed for use with SQLite. Although there may be someway to take advantage of some of its framework for use with multi-user databases, I can't recall seeing any writeups on that. I suppose one could use distributed objects to talk to a remote machine, but that machine would have also have that capability and you would have to take care of multi-user access. A very difficult task.

    It still sounds to me that you want to look into using MySQL or PostgregSQL. As I recall, they take care of the networking to a remote database via some basic setup.

    If you are planing on avoiding the multi-user database tools for the networking and plan to manually pass XML or some other form of data between the client and the server, then you might find it easier to use a web server and custom middleware code on the server. In that case you'd look into the appropriate frameworks for that. What frameworks and libraries will depend on your back end.

    So, I think you need to say more about your backend service ideas for people to give better responses. Do you want to talk to an SQL database directly? Do you want to use a web server and CGI APIs? Do you even know?

    Understanding more about your application idea will help also.
     
  25. BadWolf13 thread starter macrumors 6502

    Joined:
    Dec 17, 2009
    #25
    A) I don't know squat about network programming

    B) I need multi-user access to this database.
     

Share This Page