I’ve been working with MS SQL Server for a while, so I’ve got some ideas on how to pursue the following situation, however I’d like to get someone else’s opinion before I jump into a huge mistake.
I’m designing an application, using Visual Studio.Net. This application will have a MS SQL Server backend. This program will need to support multiple projects, which may become very large.
I need to keep information about individual projects separate from each other, as they may or may not be related. One option to the end user may be to use the File | New Project command menu to create a new project.
My question is, of the following solutions, which would be your recommendation, i.e. which would be better for the server itself.
Create a new MS SQL database for each project. Running the File | New Project command would create a new database on the server with the name specified by the end-user (i.e. the project name and database name would be the same).
Store all information related to all projects in a single database, and try to filter through them by giving each record an identification number, for example, “Project1” and “Project2”. When accessing data in the database filter all records and only display/modify records that correspond with the current project.
I thought this would be more of a SQL Server issue, then Visual Studio.Net issue, as either way will work, for the most part, fine within the application itself.
I think the second solution sounds a little, harsh. In my opinion it would do nothing but slow the server down considerably, however I’m asking this question because I was informed that running multiple databases for a single application on a single server is not recommended.
Of course, if there is another recommendation that you have in mind, please feel free to voice it.
the second solution will not slow the server down if you index the appropriate columns
the first solution makes it next to impossible to run a single query to extract data from all projects (e.g. how many projects are there, which ones are nearing completion, etc.)
I understand where you are coming from. I guess I also want to state that the projects should never be related. For example, instead of thinking of a project think of different business.
Take QuickBooks for example. It’s a business management system. You can use it to manage as many businesses as you wish, however each business has its own file, because businesses would most likely be completely unrelated. You would not want to take the chance of mixing information between the two businesses up. You don’t want to calculate the total sales of one business and by mistake include sales from another business, which would return an incorrect result and could cost the business to loose money.
I think for me to use the word project was a bad choice on my part, when actually I should have made it clear that each project would actually be an independent “business”. If I had four businesses being ran on one computer, each business having their own customers, would it make since to have each business use its own database?
I feel that if I had a total of 25,000 customers for all four businesses and each customer had not only a customer id, but a business id, when searching for customers it would be an extra step to filter for and it would slow things down…
I’m also not sure that I like the idea of saving records for multiple businesses in the same database, it doesn’t seem like a good idea to me.
Do you still recommend I use a single database, or based on the new information would you change your recommendation?
Easier to manage (only one DB to backup and restore)
If you change your application/tables you only have to change it one place
Cons to keeping in one DB:
Hard to manage resources. For example, if you have one client making HEAVY use of the system it is harder to identify and fix hotspots (e.g. you can set up a cache for one table, but to my knowledge you can’t partition the cache so that only client A’s data is in the cache and not B’s, which might not be used, ever)
Security is more difficult if you give clients access because you must ensure that client A cannot access client B’s data. Also, your application needs further workings to ensure that the frontend abides by these rules (so maybe you’d set up a group and have each frontend log in with that different user to ensure security)
Pros to multiple DBs:
Can move DBs to different servers without significant hassle (the application wouldn’t have to change)
i personally would not use multiple databases unless (a) forced to, or (b) paid to
then again, i cannot admit to ever building a site large enough so that you’d notice the difference in performance
it’s good to think ahead to the day when your app is so successful that you have to have multiple processors, multiple servers, multiple data centers, multiple offices worldwide, etc.
but for a few million rows? the server will not suffer performance problems if you put everything into one database
you said “it would be an extra step to filter for and it would slow things down…”
Thanks for the replies. I can give the single database a shot and see how that works out. It will be somewhat different then anything I’ve done before, therefore, I estimate it may take a little longer for me to design the front end.
An example query might look something like.
'All records from project 123
SELECT * FROM Customers WHERE ProjectID = "123"
'All records from project 456 where customer id is 001122
SELECT * FROM Customers WHERE ProjectID = "456" And CustomerID = "001122"
'All records from all projects
SELECT * FROM Customers ORDER BY ProjectID
I’m not one hundred percent sure on the exact syntax, but I think it’d be somewhere along those lines.
I think that if I spend a little extra time on the front end it will turn out fine, and I’d learn a new technique as well.
The more I think about it the better it sounds. That’s why I like to get someone else’s opinion, sometimes I get stuck in tunnel vision mode.
As usual, I’ve been asked to change the design again… Now I also need to provide support for an Access backend, for various reasons.
I think I’ll give the same idea a shot with the Access database, using one file stored in the applications directory to hold all the info… May not be the best idea but I don’t foresee how this will turn into an extremely huge application.
I also don’t like the idea of having the application perform to completely different functions depending on the type of backend used. It makes it a lot harder for code reuse, etc.
I agree, I’ve built Portal solutions for colleges that manage thousands of users with millions of rows of data in some 60 odd tables. They’re all stored in one database. I’ve built apps for Merrill Lynch, Mellon Financial, Zagat, TV Guide, Microsoft, Qualcomm, etc. and have never had to use more than one database because of scalability, performance, etc.
In my experience multiple databases for this scenario is the way to go.
Since none of the information about a business is related to another business it is a logical way of segregating the informaiton.
It makes the development and management of the database easier by not having to carry a business id on every table.
If one business grows to be huge queries for the other businesses are not slowed down because of the extra data.
It is easy to scale and move a complete business to another server.
It also gives your customers more confidence because there is little to no chance that someone will get access to their information because of a programming mistake.
I have personally worked with both types of databases, some with millions of rows used for reporting. In the scenario where one entity had 10 million entries and another had less than 200,000 the smaller customer was inconvenienced greatly by the extra processing time.
Yes, you can say that you can tune the indexes, but the fact is that this doesn’t solve all of the performance problems.
I’m also for multiple databases. Ease of maintenance and segregation of data is very important to me.
The only situation I would avoid using multiple databases is if I would need to query data that spans across more than 1 database - in this case, the headache would be bigger if I kept multiple databases.
Now I also need to provide support for an Access backend, for various reasons.