Choosing a Data Store

Regardless of what you’re doing, you’ll probably have to save some data sooner or later.  When you do, you’ll be faced with an incredible array of data technologies, all of which are clamoring for you to use them.  The data store you choose will define your entire project.  Choosing wrong dooms you to failure.  But until you’ve had a hell of a lot of experience – and made some horrible mistakes – it’s pretty tough to know what to pick.  Here’s some help.

The first thing you’ll need to decide is whether your data belongs in a file or on a server.  This is where a lot of people go wrong.

Files are great when the data is specific to one person and one place.  The data is saved in one file, an easy bundle to manage and understand.  Your data will be easy to backup, easy to send to other people, easy to copy and archive and transfer.  Everyone understands files.  If your program has a File Open command, you probably want to go with a file-based store.

But files have weaknesses.  They’re fragile – lose the file, and you’ve lost everything.  This is a bigger problem than you might expect: if your hard drive hiccups or if someone bumps your network cable or if your power goes out at the wrong time, you might lose everything.  They’re very difficult to share.  Security can be an issue – you’re pretty much at the mercy of the underlying OS.  They often don’t perform very well when there are many read or write operations on the underlying file.  And perhaps most importantly: it’s just a bunch of bytes.  It can’t do anything but sit there.

Servers, on the other hand, can do all kinds of amazing stuff.  In fact, it’s just about possible to write entire applications using nothing but a database server these days.  They’re great at coordinating data between many different people and places, usually have lots of flexibility when it comes to security, and often offer much more extensive feature sets than file-based storage.

But, they have their weaknesses, too.  Obviously, you need a server machine.  Maybe that’s just the machine your program runs on anyway, but even so, you’ll probably need to install some supporting software there.  They’re much more complicated to manage.  Since the data is exposed as a service, you can’t nicely bundle it up and e-mail it to someone or burn it to a CD.  And there’s often lots of overhead associated with the server.

Generally speaking, if you’re writing a program that just one person uses (at a time), and you’re working with easy-to-isolate chunks of data, use a file.  If you envision your program opening documents of some type, you’ll want to use files.  If you’re building a stand-alone program that will be installed on many computers, you probably want to use files.

If you need a bit more power, reliability, performance, security, or interoperability than simple files provide, then move up to a server.

Of course, most complex solutions support – or require – more than one type of data store.  Simplicity is good, but don’t feel that you have to keep everything in one place if it doesn’t really make sense.

I’ll be going into much more depth on this topic later, but for now, take this away:

Need a server?  Use Microsoft SQL Server.  This comes in so many flavours, something is bound to work for you.  From the full-blown Enterprise edition (or the virtually identical – to a programmer – Express edition available for free), to the file-based Compact Edition needing only a small DLL to run, it’s a good bet.

Need a file?  Perhaps SQL Server is overkill.  If so, use XML.  XML is very flexible, with a great tool set.  You can even go between a DataSet and XML with one line of code.  Another safe bet.  Be good: save it in an appropriate location under %AppData% or %UserProfile%.

Saving simple user settings?  Particularly the type that don’t really need to be saved or backed up, but merely offer convenience?  Use the registry.  But use it properly: save in the right place, and don’t save any serious amount of data.  If you feel you have too much data for the registry – or you just don’t like it – then save an XML file in %AppData%.  But still: don’t save anything here that the user might want to backup.

Of course, this advice is only valid most of the time, and as a developer you should know that a solution that works most of the time is worse than no solution at all.  Nevertheless, if you’re making a different decision, it wouldn’t hurt to take a moment and think about it again.