Which strategy to choose for building web based business application

Question

Hi everyone!
I'm planing to create an accounting application in asp.net mvc.
Each user will pay monthly subscription, I'll provide daily backups, etc.

I don't know which strategy to choose:

1. To use SQL CE4 and run application in separate virtual directory for each user.
2. To put everything in a single SQL Server database

What are cons and pros of these two options?
If I choose option 2, I'll have to write more logic inside code. I must prevent users from seeing things they should not see (more trips to database).

The two most important things are:

1. General performance of the system
2. Ability to easily create backups for separate users.

I expect between 20 and 30 users.

Any suggestion will be appreciated.

Answer 1

I'd go for option 2 as always. I come from an intense financial integration background so option 2 (to me at least) is best.

There are no con's when considering the use of data. There are no more round trips than there would be with option 1.

Performance of the system is generally left up to the programmer. If you write shitty code you can expect terrible performance. Option 2 provides the easiest backup option as you only have to worry about 1 database. There's no need to consider that for separate users at all if you go with 1 database.

RE: the round trip's for users (or more "logic" in your code to handle that), how hard is it to add "Where userID = x" ?

You should be separating your .net code with stored procedures anyway so writing from the ground up shouldn't have anymore (or even less) round trips/file access issues than you would have with option 1.

Answer 2

You are probably not going to realistically be able to use SQL Server's backup feature if you go for a multi-tenant design in a single database, because the unit of backup is not going to be fine enough to back up each user's data individually. You can use partitions and filegroups and backup only filegroups, but the partition feature is not exactly easy to administer, and would probably not be good for this purpose.

If you are expecting to use SQL Server's backup and restore capability independently for users (which it sounds like from your comment), you probably need to have them in separate databases.

Having said that, I would re-think that requirement (think about implementing selective export/import), because a multi-tenant architecture is a lot easier to deal with overall.