Shared Database XML vs SQLite vs SQL Server CE

Question

I was wondering which of these would be best suited for the needs of my application. The application would be distributed on multiple pc and share a common database. This database could contains up to about 20 000 entries. Basically it is simply a meeting manager and each entry in the DB would represent a meeting.

For instance, I might want to show every meeting involving a certain person or every meeting with a certain object. That's not really important at this point since this is only the Data representation I might want to make.

The fact is I have certain limitations :

• I'm doing the application in C#
• I can't install anything on the users' pc (I must only copy the .exe + dll if needed)
• The Database will be locate on a network storage and might be accessed by up to ~10 pc at the same time
• I have never used SQL Server CE nor SQLite (nor any other DB in fact)

My first idea was to use a pure XML database since it's pretty straightforward with .NET class serializer. In order to modify the database, a user should have to acquire write access on the file. Meanwhile, every other users would be in read-only. At any moment they could know who owns the write access through a file created upon getting this access. Each application in read mode would also have a running process monitoring the XML file in order to notify its user if any change were made to the database, letting the user the ability to reload it.

Having heard a little of SQLite and SQL Server CE, I'm not quite sure I have the right approach in term of scalability and performance. I don't mind learning how to use them. My main concern is mainly the fact that I really can't install anything on the users pc and I want to make the deployment of the application as simple as possible for the users (ideally, copy/paste the application folder).

So here are the questions:

EDIT 2 : I guess it was closed because it might create a debate over SQLite vs SQL Server CE which is not what I seek

1. In a context like the one presented above, is it better to use a XML file or a true database ?
2. What benefits does "true" database give over XML outside the performance once we have many entries ?

Similar Question: Xml or Sqlite, When to drop Xml for a Database?

EDIT : Here is more information for you in order to help you understand the context I'm in :

The actual system used is Word. Everything is written in a file in a folder shared between some users on the network. The users retrieve the information from this file to create other files according to the Data representation they need. I want to create an Application that will ease the process for them. Since they are used to their system, if my application need any kind of manipulation in order to install it, they'll probably want to stick with their system. Each installation need to go through their IT service which is slow and complicated especially for a non official application.

Those pc probably prevent their user from doing anything outside any common task. That's why I want to make my application as simple as possible (so I can pass the executable and dll over a USB key and it can run as is with minimal user interaction)

Answer 1

According to your requirements, you need a database which handles the following:

1. Handles concurrency
2. Work properly through network. file sharing is not a good practice and databases which works in the network must have an DBMS to control the access.
3. Transactional
4. User Management and security (Authentication/Authorization). you need that as long as you expose it in the network.

Based on all of your requirements, I believe none of the SQLite/SQL CE/XML file are the good answer. All of them are useful in situations that you want to have local database (not through the network). As soon as you want a network-based database, you need something more advanced, as I mentioned above, things such as Concurrency, Security etc. I suggest you can use SQL Express 2012 which is free, scalable, secure, network accessible and fairly easy to work with.

As the rule of thumb, when you have 10 remote users, it's very likely in the future they will be increased. So, go for the stronger options, as changing the DB is not the easy task later on.

Answer 2

A database like Sqlite would take care of the multi-user read/write problem. An XML file will need to be cached in memory and written serially everytime the data changes. Even if you come up with an external strategy for ensuring only one user writes to the file, other users could be reading while the file is being updated.

Finally an embedded database becomes more efficient the larger the amount of data you're working with.