January 9, 2009 at 10:45 pm
I am designing a web service for hom e inspectors and using SQL to store text and images from each inspection. The data is later used when the inspection report is viewed online or when inspection company analyzes the business data over time - reporting.
I think that I should generate a new database copy for each new inspection company that joins the service. The inspection data is private and there could be multiple inspectors per company.
I'm looking for guidance on BP here. Is there any reason why I should want one huge database for all inspection companies or is it best to separate companies across multiple databases?
First major project and need direction on how to think about this.
Mort Strom
January 12, 2009 at 7:35 am
Unless you need to have schema or procedures different based on the inspection company, I would put everything in a single database.
First, every time you get a new inspection company, you have to generate a new database - either manually, or automatically. This means you have to set up maintenance, reporting, etc. on each new database. If you take on new business regularly, this can become a problem.
Next, if you ever want consolidated reporting (perhaps for yourself) you would have to collect data from multiple databases.
Finally, if you make a change to a stored procedure, or modify some schema, you will need to ensure your databases are all synchronized. Unless you buy or build something to manage this, they will slowly diverge and turn into a code maintenance nightmare.
Keeping things apart in a single database is relatively easy. It can be as simple as a CompanyID on every table to identify who's record you are looking at.
On the other side, if you want to be able to have different schema or different code - multiple databases may be the way to go. It would allow customer-specific customization (and all the overhead that goes with it). You could also load balance pretty easily by having customer databases on different servers.
January 12, 2009 at 9:42 am
I disagree with Michael's suggestion, although he is correct in all that he says, there is one key point he didn't mention, security. If you put all the data in one database you need to manage security in the application. If you use separate databases then security is handled one time, on login to the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 12, 2009 at 9:46 am
this question has also been asked and answered here;
http://qa.sqlservercentral.com/Forums/Topic634281-1291-1.aspx%5B/url%5D
January 12, 2009 at 11:02 am
The security issue is not difficult to handle through Stored Procedures and Views. I have experienced the SQL 2005 with 120GB single database for multiple customers with high number of concurrent transactions, it works well but depends on your designe. I partitioned my large tables when the db size reached around 80GB on produciton server and now as the per month data groth rate is around 7GB I start thinking to move on multiple databases. I have complete design for it how it will work. But beleave it or not it is not easy to manage easly.
I don't recommend you to go for single database for each customers on this early stage until you have clear idea of your database grouth rate, cuncurrent transactions, hit rate and number of customers. The most importent factor you need to consider how much changes your are expecting after moving to production and what will be the expected frequncy of changes.
even if you are expecting large data size but concurrent transactions and hit rate is low you should use single database. if you are expecting the high frequncy of changes then you should not move for multiple database.
my suggestion for you is, in first short go for only one single database for all customers but keep your database designe flixabel and scalable so that in future you can ealsy move to multiple databases with minimum changes if needed. make sure you are using GUIDs for primary keys and observe/analyze your db when it is in produciton before you take decission of going for multiple databases.
January 12, 2009 at 11:28 am
I would tend to separate them. One main reason is that, if you expand to a point where adding a second server makes sense, it's a LOT easier to move a few databases from one server to another, than to either split a monolithic database in half, or to set up a database to run on two machines at the same time.
As far as setting up new databases being problematic, I don't think it'll matter. If you create an empty template database, with lookup tables populated and nothing else, it'll be easy to make as many copies of that as you like.
Also, if you have tables that are the same across all databases, you can put those into a singe central database (I usually call that database "Common"), and have the customer-specific databases link to that.
Splitting the databases also has the advantage of making it easy to handle things like a customer calling up and saying, "Hey, we accidentally deleted a whole bunch of stuff that we shouldn't have, can you restore our database to last night's backup?"
There are a number of other advantages.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 11:46 am
i am not disagree of the advantages and I know good details how to manage multiple databases but in my books to split the customers data never use multiple databases just for the sake of having multiple databases until and unless you have real need for it.
January 12, 2009 at 2:21 pm
My proof-of-concept service has been in production for 2 years now and the db size is 125MB (1 company, 2 inspectors). As I design the 'real' online service for multiple inspection companies, my big concern is for performance, scalability and manageability.
When considering a single database, why is it important to used GUID primary keys over/against an integer identity?
Thanks for any direction.
Mort Strom
January 12, 2009 at 10:09 pm
Oky I didn't had idea the service is in production for last 2 year, in 2 year 250MB per customer is nothing 🙂
GUIDs are always unique cross databases but nevertheless integers can work for you if you already thought of the uniquness issues.
Anyways if you are planing to go for multiple databases alwys use one single database for inital authentication, after successfull Login this main database will give you details (sql logins, server, db name etc) of the target database for the customers and store your basic customer's and common information in your main database just like GSquared
has suggested. Obviously for these common Customers and users tables you need to use the primary keys as GUIDs so that you can reffer the same GUIDs cross your databases.
January 13, 2009 at 10:57 am
I agree with the consensus that one big DB is much easier at this stage. I would suggest you include a customer_id (or whatever the PK is at the level you'd consider splitting the database) on every table except code tables (standard tables like COUNTRY, STATE, workflow states, etc). That way you can more easily split up the database later and can easily add one line to the where clause of each query or stored procedure to provide at least as much security as multiple DBs would give you. If you do the where-clause security thing, you probably want to index the customer_id column.
January 13, 2009 at 11:54 am
First of all, it's important to approach this question from business perspective rather than dev/admin perspective.
What are your expectations for business growth? A new customer per day or a new customer per month?
Do you expect that some customers might require unique customization/reporting, etc?
Do you expect that some customers might request their data to be available on their site - i.e. a weekly data copy for them.
Do you have plans for using third-party tools, in particular reporting tools that would by-pass your application data access layer and talk directly to the database? Will you give customers ability to write their own queries/reports?
And, of course, as already mentioned, security and customers separation requirements.
From my experience if you expect any significant growth to your business I'd suggest separate DBs. This way you'll be better prepared to address individual customers' requirements, which really can be quite unique 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply