2

I am tasked with finding the best way to store data in the cloud. The application will be used by thousands of small, autonomous non-profit organizations. The data for each organization will need only a few (4 or 5) tables. The largest table for the largest organization will contain no more than 3000 rows; the largest table for the average organization will contain about 200 rows.

Each organization will need to access its data daily, but the number of transactions will be limited. It is possible (but not likely) that many organizations – perhaps hundreds – will need access to the data at the same time. Only one person at each organization will need access to the data.

Data storage costs are extremely important – these are small non-profits with minimal funding. The data will be accessed from a C# Silverlight app.

I am open to any solution; I’m not locked into using a traditional database.

This is a one-man, part time project without time sensitivity. I do not have access to database or cloud specialists; although I’m an experienced developer I have little database and no cloud experience.

Again, storage costs must be kept to an absolute minimum, ideally no more than few dollars per organization per year.

Can you suggest how I might accomplish this?

Thanks in advance!

casterle
  • 1,037
  • 2
  • 12
  • 27

4 Answers4

1

Use DropBox (free < 2GB) and a SQLIte database file.

Peter K.
  • 8,028
  • 4
  • 48
  • 73
1

I would suggest SQL Server Express or MS Access as Database.

gsharp
  • 27,557
  • 22
  • 88
  • 134
1

Unless I'm mistaking something, it seems like you could host a good old database per customer on a cheap host (or use a single database with multiple schemas, if it your favorite one allows that). It'll only cost you a few bucks per month. Surely your sales revenue would cover that, no?

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Thanks, Denis. My current host charges $10/month per database and does not allow remote connections, so I’d written off this approach. Tim raises a good point re: schema changes. Do you know of a good way to handle this problem? – casterle May 24 '11 at 13:58
  • I'm used to doing it the gory way, personally: changing the schema of individual customers as they upgrade. The key part is as they upgrade (there are cases where you don't want the schema and the app to not be in sync). – Denis de Bernardy May 24 '11 at 14:20
  • Btw, your $10/month/database host can/should be changed. There are plenty of cheap VPS providers that will allow you to set up any number of databases and remote connections. In the case of PostgreSQL, there even is one ([hub.org](http://hub.org)) that is run by one of the initial core developers. – Denis de Bernardy May 24 '11 at 16:35
  • Thanks for pointing this out. I made the bad assumption that all hosts would be similarly priced. Ass-U-Me! – casterle May 24 '11 at 18:29
1

It would not be a good idea to store the data from each of your thousands of non-profit organizations in the same single shared SQLite database, as that would lead to concurrency issues even if only one user per non-profit was using the database. Neither would it be a good idea to give each customer their own dedicated SQLite database because then when you make a schema change (difficult enough already with SQLite) you have to make the same change thousands of times. It would be best, I think, to create a single database but give each non-profit its own "virtual private database". I recommmend that you get a price quote on an Oracle-based cloud database, because Oracle has had this "virtual private database" feature for years now.

http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/vpd.htm

Tim
  • 5,371
  • 3
  • 32
  • 41
  • Thanks, Tim – this sounds interesting. I’ve always associated Oracle with solutions orders of magnitude larger than this project. Do you know of a host who might be interested in dealing with such a small application? I’ve been unable to find one on the ‘Net. – casterle May 24 '11 at 13:54
  • Thousands of customers each with its own data does not really add up to "small" --at least not in terms of your ongoing support and maintenance burden. If you have absolute control over the user interface to the data and there are no side-channel ways to get to the database, you could implement the virtual private database functionality partly in the client and partly on the server, using views, maybe; but that would be a lot more coding and a lot harder to maintain than a database security policy. A client-server database designed for multi-user concurrent writes is required in any case. – Tim May 24 '11 at 16:42
  • I was thinking 'small' in terms of total data, but of course you're right. But small is also correct in terms of budget! I get the impression that the hosts I've found so far are targeting enterprise scale customers. – casterle May 24 '11 at 18:27