Whats the best way to determine when it is appropriate to use a database?

Question

I began my programming career in web development using PHP and MySQL. I got quite accustomed to utilizing db for storage of most dynamic data as well as some setting/parameter data. Sometimes there would be a lot of data while other times entries in the tables would be few. To me this just seemed natural and as far as I'm aware this is more or less an acceptable approach in web development. (Please correct me if I'm wrong...)

I'm delving into desktop applications now and my natural inclination is to again utilize a db to store a lot of information that will be generated through the usage of the application. However, as far as I can tell I don't see applications (that I use) utilizing a db very often. [EDIT: It has since been pointed out that this was a faulty assumption in that many applications do use lightweight dbs embedded into the program itself.] What is the reason for this? At what point is it appropriate to utilize a db? Are there any standards on this matter? Also what are the reasons to NOT use a db to develop a desktop application?

Answer 1

if your application is document-oriented, store things in a document file

if you application deals with more structured data, too much for a single document (like an XML document), use a database

Answer 2

A database system has traditionally been seen as a relatively heavy service - that you didn't necessarily want to install on every client machine out there. I've actually been in situations (desktop GUI app development where a lot of data needed to be saved) where a real DB system would have been an "ideal" solution - but because back in the day the only available databases were relatively heavy, we went with flat data files instead.

Despite there being some lighter databases out there these days, this is still a valid argument against client-side databases. Imagine some simple little desktop app (such as say, a simple desktop toy or game) that has to store a few dozen settings and parameters. It seems way over the top to make a person install MySQL just to run your app.

With web development, the server is naturally a back end, where having a database is par of the course. eg. The users don't have to worry about installing a database at their end.

Answer 3

Desktop applications maintain state while they are running. Whereas web development typically doesnt. So while you might need to store a user setting in a database in between page loads, in a desktop app you just keep them in memory. That greatly reduces the need for such types of persistant storage. When you want to store preferences between uses, you can save them all to one file, or put them someplace like the Windows registry.

That being said, database systems are used quite a lot in desktop apps. Long before the web, desktop applications have been connectiong to DBMS's. Primarily for applications that are not document based. Though these days, with the better availability of light weight engines, you're seing the lines blurred a little bit. For ex, I use SQLite db's as documents in a few of my apps.

Answer 4

One thing you might want to look into are lightweight databases that don't require an actual running database service. e.g. on the Microsoft front there is SQL Server Compact, which is free, only requires a single file for the database and some DLLs added to your app, and from a developer's perspective, behaves much the same way as a "real" SQL Server.

I am positive there are similar options on other platforms.

Answer 5

On web applications, it's important to store any persistent state on a centralized storage. Since the web app is usually first bottleneck, it's important to be able to just distribute it without having to question which copy has the data (the 'Shared Nothing' principle). Not just a database, but memcached and queue systems are there for the same reason.

Desktop apps don't have the same scalability goal. Typically, most of the data is locally stored on each workstation. Data sharing is a different issue in most cases. That eliminates one big reason to use a database.

GUI applications also can have complex documents which might be handled as a complex web of objects in memory. Normalizing the structure into a relational DB schema can add significant complexity to the problem, sometimes it's easier to just serialize the whole thing in a single bytestream. Many OOP frameworks include some facilities just for that.

Still, making the stored documents readable with tools outside of the main application is a big advantage in many cases, so either using human-readable formats (XML, JSON, YAML, etc), or a zipfile amalgamating several simpler pieces are getting more and more common.

On the same vein, using an embeddable database library (BDB, Tokyo Cabinet, SQLite, MS-SQL*server Compact, etc.) is another great approach.

Answer 6

Databases can be overkill but they usually don't have to be. Very simple programs just don't need them. If your document can load up in memory in a trivial amount of time and stay there without issues, you don't really need one for many programs.

For instance consider:

#include "superheader.h"

int main()
{
    int row=2, column=2;
    DatabaseType db;
    db.ConnectTo("programDB", "user", "pass");
    db.setTable("messages");
    string hello_world=db.fetch(row, column);
    cout<<hello_world;
    return 0;
}

Well that is overly simplistic, but obviously this is just too much. There's no real point to having that level of data management for "Hello World"

Usually the rule of thumb I go by is use a database if you have many sets of data, especially if they are uniquely different OR use a database if the amount of data is rather massive. Databases in general are associated with some overhead, but there are many which don't really have much. Small, lightweight, in-memory databases for instance are sometimes useful for small projects with heavy processing, scheduling or lots of dynamic changes to the data.

There are different coding styles, many many times there is nothing wrong with a database but most of us just wont go 'that far' for basic tasks. There are still other times the right database would offer performance benefits. Try to especially understand the differences in where the data will be, how long it will be there and what will change it during it's lifespan.

Answer 7

Using a db is always the right thing and with SQLite implementations for practically every language out there not using one is just poor design decision making. The only reason not to use one is if you're doing embedded programming or some other type of non-application form of programming. Querying data and settings with a declarative language like SQL is much more natural than traversing flat files or other objects in memory with imperative syntax. Plus, it cleanly separates the data operations from other application code which in the long run makes code more modular and maintainable.