XML vs. SQlite vs. Access

Question

Question: We've started a project for a customer, which includes what would normally be done with a database.

However, the customer wants no database installed, since it's only a little application. We however intend to reuse the code for a larger project, which will use a database.

The problem is all the server side code will be different if I'm using XML or SQlite or Access.

I'm tending towards SQlite, but I don't know. Would adding the database in an MS-Access file be a better solution? If I would put it into an access database, does the customer need MS-Access installed or only the MSFT MDAC ? If I use the Access DB, will that run under Linux with Mono, too, or is there no MDAC replacement ?

Answer 1

I would use SQLite over Access - for SQLite you just need to ship the DLLs, for Access you need an installer (although I think it's built into recent versions of Windows). Also SQLite is a better DB.

Or you could of course abstract the data stoarage mechanism using the Repository pattern or similar? If you were to store the data in a file, then load it and expose it from your repository using Linq-To-Objects, then later you could just drop-in replace that with Linq-to-SQL without changing any of your client code.

Answer 2

I would consider using NHibernate, you can hook it up to SQLite, but can upgrade later to a full database later without needing to change much code. If you're not keen on this, I would use SQLite directly over XML files.

Answer 3

You do not need to install Access to use the Jet engine. Jet is installed with all recent versions of Windows, as far as I recall. You can use Jet (Access) with Linux. Jet requires very little in the way of database management compared to the alternatives, including SQLite.

Answer 4

All recent versions of windows all the way up to windows 7 have shipped with a copy of JET (the ms-access data engine). So, you don't need mdac, nor to install ms-access at all.

You can even write a windows script on a clean windows box without any software installed and thus you can read data from a mdb (ms-access) file.

So, the issue here is not installing, since "JET" (the database engine that ms-access uses) is shipped with windows anyway.

I think the only exception or consideration here is are you planning to work on 64 bit editions of the os and using in-process 64 bit applications?

There is a 64 bit edition of ms-access for office 2010, and as far as I know, there also going to be a separate download for installing the JET (now called ACE) engine on 64 boxes.

I you planning to only work on 32 bit machines (or use 32 bit editions of your software on a 64 bit box), then you not need ms-access nor will you need to install anything to read and use mdb (ms-access files).

Since JET ships with all recent versions of windows, the you decisions will not be based on installing JET (you don't have to), your decisions will be other issues and if JET meets your requirements.

Answer 5

You might have issues using sqlite in medium trust environments (in shared hosting for example). If you're opened to other solutions, you might consider giving VistaDB a try. It is supported by all major orm (nhibernate, openaccess, entityspaces, subsonic and many more ..).