Which is better single SQL DB or multiple SQL CE DBs in this scenario

I have a website that will be used by people开发者_开发问答 to store their business statistics and generate reports out of them. some of these are related rarely. these sets will be used rarely by multiple users. there were three designs that emerged

1. use a single SQLDB and store all users data in the same and generate reports from there.
2. use one SQL CE db for each information set.
3. multiple SQL Server Databases one for the set

the application is in ASP.NET and will be hosted as public service. the traffic is expected to grow to thousands of information sets. please suggest best and alternates if any

---

I would say one sql server db and then use multiple "instances" or "schemas" to separate out the different users. Because people are uploading their business data, you want to segregate out that and make sure that it is pretty impossible for one to access the other. You will probably want to do some impersonation or something like that to keep all the connection details (as it'll be different users or something like that per client) all separate.

I don't think CE was ever designed for a lot of data and I know that it just stores everything in flat files. You will probably want to use sql server to take advantage of all the special features (read SSIS and SSRS).

Multiple sql server databases would have a huge huge huge overhead and you would need a lot of back-end power.

---

Complexity of finding records in DB by index is about O(log(N)) So if you store all data in single DB each user is paying price of sitting in the same DB as others as it is a function of total number of records across all users. Logarithm (or so) but still.

Therefore separate DBs are better scalable. You even can update structure of DBs one-by-one without stopping the world. Or put additional boxes serving their own range of users. If needed of course.

That is theory. Practice depends on how effective your file system in finding files and what is the cost of opening/closing the DB. AFAIR in SQLite these operations are relatively cheap.

---

I've done it both ways for different apps and my experience is that, somewhat counter-intuitively, it's better to have one database and mix the data. One of the cases was for medical billing information, displaying and maintaining patient records for multiple practices, so security and granular access were critical.

Some reasons:

Any changes in schema design can be implemeented once in one place.

Ditto software changes that exercise the database interface. (Sure you can abstract this away for the most part, but who really does?)

You are creating a requirement to implement a bunch of software connectivity switches to handle customer distinctions that are easier and cleaner as fields in tables.

It's simpler to administer fewer rather than more databases.

When adding and removing customers, it's the difference between adding a row to a table and installing an entire new database, with all its administrative and maintenance resources and configuration.

(Note: This is also featured as one of the "SQL pessimizations" in one of the threads on that topic.)