[utilizer]

How efficiently would an SQLite database run on 10.4?
The purpose would be to create an accounting program, with many sophisticated features, most of which would be dealing with inventory and analysis of financial data.
Since SQLite is part of the Core Data engine in Tiger, it seems it would run very fast but I'm just getting into programming for the Mac.

Am I better off using another relational database foundation, perhaps PostgreSQL?
TIA!

[larkost]

The number of records and whether you wanted to have network abilities would be the deciding factors in making the SQLite/PostgreSQL decision. The latter is going to be for networked apps, and the former for below some number, say... 10,000 records (the actual number is hard to decide, so I just pulled that one of of the air).

SQLite is a relational database. And it seems to me that if you don't understand databases, then CoreData will take care of a lot of things for you.

[techtrucker]

I almost hate to respond, because I'm just starting to tinker with Core Data, but...

I'm developing an app that is quite involved, and is probably as complex as an accounting package. I started developing the app before Core Data and even Bindings, and after evaluating the use of the new technologies I've decided to stick with the tried and true: PostgreSQL. I've created my own Cocoa framework for psql's C API, and even though some folks will shy away from writing glue code, at this point I do most of it in my sleep, and in some small way (probably more to justify sticking with older technologies) I feel that my app is pretty "lean and mean". But as always YMMV and I would encourage writing some test apps to see what you think.

[utilizer]

Very nice...I especially like two separate point you both made.
First, I forgot to mention that this will be a network-able accounting package and to learn that SQLite is not, that really pushes the development of this app to be built around a PostgreSQL structure.

Second, I like the fact about the reliability factor with PostgreSQL. There is nothing more disappointing than spending all of the time in world developing an application built around resources that can't handle what you're trying to make the app to do, even though it might be easier to develop in the short run. But in the long run, you'll end up spending way more time when cutting corners.
I'm glad we started this discussion! Now it's time to start planning and developing!

[WJMoore]

Another option might be write an abstraction layer that would allow you to plugin Postgres or CoreData. That way you could test both and also give the user the option to not require Postgres to be installed if they didn't think they would need it.

[techtrucker]

WJMoore makes a good point, I just wouldn't even know where to begin...use CoreData but with PostgreSQL...hmm. I don't know enough about CoreData yet.
What I have done was to create a generic database connection class in my app that accepts sql statements as NSStrings. If, down the road, I wanted to use a different RDBMS I would only have to change that class.

[Brass]

You can't pass SQL to CoreData. You must pass CoreData's own predicate formulated queries. CoreData then converts these to SQL internally (if using SQLite) or to another suitable format for use with it's XML or binary formatted document files.

SQLite is exceptionally fast. 20 times faster than MySQL, etc (in some situations... according to their website). But then it is a completely different tool. It is not designed to be used as a database server, but rather to be embedded within an application to give the application direct access to an SQLite datafile(s).

I started writing an accounting package based on my own custom built Cocoa wrapper around SQLite. I got it to the stage where I could test the speed and I was very impressed (with SQLite... not with my own code). It completed a reasonably complex query of 10,000 records in a view using multiple relationships and NO INDEXES in 0.3 seconds (I set up my code to log the times of each query).

So then I thought "great, I'm going to be able to do some great stuff with SQLite in Cocoa!". Then I went to WWDC in 2004 and heard all about CoreData, and decided to completely halt development of my own SQLite wrapper framework, and any dependent applications until Tiger was released, and use CoreData instead.

It's WAY COOL! The modelling tools, and the abstractions from having to deal with (the exceptionally primative) SQL directly are such a bonus. And to have it all integrated in the NSDocument (actually NSPersistentDocument) class in Cocoa should make it very easy to do.

I've not yet begun working with it (don't have Tiger yet), but I'm very much looking forward to it.

Having said all that... sounds like you really need a more distributed system, for which PostgreSQL might be more suited.