I'm building an ASP.NET MVC application. I will store a lot of data per application user, so I was considering having separate DBs for each user's data. Of course all DBs will be 100% the same in terms of structure. They should all be derived from some kind of master DB. Can someone give me any leads on how to realize this?
1
-
1I don't think you should use a database per person. With proper indexing and a smart design, you should be fine. There are a lot of databases out there a lot larger than the one you're about to design that don't have a db per person.Yatrix– Yatrix2012-05-18 13:56:03 +00:00Commented May 18, 2012 at 13:56
Add a comment
|
2 Answers
A database per user sounds a terrible idea for a lot of reasons. If the data or reads/writes are huge you should consider sharding (splitting the database into separate databases based on primary keys but NOT a separate DB for each primary key).
If you are using SQL Server this can handle several billion rows with just a bit of tuning -http://www.sql-server-performance.com/2011/index-maintenance-performance/ for a good reference on this.
6 Comments
Ron83
I understand your feedback, but I will be using SQL Azure DBs. By sharing the DBs I separate potential performance impact caused by other users. Each user will need potentially multiple GBs of storage space. Users will pay a relatively high price for this service in the future, so I need to prevent potential performance issues upfront.
Judo
You could look at SQL Azure Federations which is sharding and will resolve a lot of your performance concerns and split the DB up by users. I am guessing your performance concerns as not as great as you might perceive. The size of the DB is not really the issue it the read/write load. I've never seen an implementation of a single application with separate DB's for each user. There really isnt a model for DB Schema 'inheritance'
Ron83
Ok, how do I make sure that users not accidentally get access to data of other users? A developer (aka me) can easily make a mistake in one of the table joins...
Judo
Separate databases wont really provide any more security. A userID is mapped to a column in a shared DB or mapped to another DB. Incorrect userIDs usually aren't an issue - but you do need to ensure all queries have a UserID. Your comfort should come in test. Just write a test to check if a query will return data from any other users.
Ron83
But technically, it would be as simple as changing the connection string no?
|
Unless you are planning to handle millions of users with millions of rows in each table you will not need to use multiple DB's.
MS Sql can easily handle a billion rows in a table with good indexing and still keep good query performance.
Using multiple DB's will most likely only cause headaches.
That said, you should design scrips to create the databases, and later update scripts for changing them since you would have to propagate any changes to all databases.
But my recommendation is to stick with one DB. If you actually manages to outgrow that you most likely need to redesign the database structure anyway :)
