Creating many small databases to be accessed by a webapp

I have this requirement for my business. We have a swing desktop application that works with a mysql database. At the end of each day the swing app exports the data that has changed and uploads it to a server. The set up is, a user working in an office, will have many companies that he is working with. If he changes any data for that company, then I export that company's data alone from the database. The data is exported in the form of java objects, serialised and stored into a file which gets uploaded.

The next day, if there are any changes made to that company again then I will replace the file in the server with the latest uploaded file.

Now on my server, I would like to work with this file. I would like to convert each of these files into mini databases that a webapp can read. It will not write to it. Everytime the user uploads, the database will be deleted and recreated.

So ultimately each of these files are a small subset of the data that a user has in his desktop application.

Now this issues are:

1. The objects that I have exported are "Apache Torque" objects. Torque is an ORM tool, basically the object represents the table. I need to convert this object into a database. Sqlite, HSQLDB, Derby...? The database should be small. If the object file is about 5KB, then the database that represents that file shouldnt be 3MB. Derby did that actually.

2. The java object classes could change. Since the underlying database could change. Hence I will need to deserialise these objects and create a database from it as soon as it is uploaded. Otherwise, I will not be able to deserialise these objects later on开发者_开发技巧. Small changes to the database is fine for the web application. But if I dont deserialise it immediately, then I am stuck.

3. The conversion from the java object to the database should be fast. Since the user actually waits when his data is getting uploaded I would like to add a maximum of 5-10s additional for the conversion.

4. Is it ok to have thousands of these mini databases lying around? Is this design okay? Is there an alternate solution?

---

I wouldn't try to put each dataset into its own database. I would put all of them in one big database, along with a column in the key tables indicating the dataset that each row applies to (this sounds like it should just be a company identifier). This is a more normalised design than having many small databases.

You will then need to write the webapp so it makes queries for particular datasets, rather than connecting to a particular database.

if you adopt that approach, you can deserialize and store the datasets as soon as they arrive. The storage is simply inserting rows into an existing database, so it should be very fast.

In addition, i expect that one big database will be much easier to manage, maintain, report on, etc, than many small databases.

If you tell us more about the details of your schema, we could discuss how the database could be organised, if that would be useful.