frettfreak
asked on
MS SQL 2008 database design for public site
I am looking for some suggestions on a database design. I am building a public site that will have a lot of users. each user will have a client database that they will be uploading and managing for various tasks. I am using MS SQL 2008 if that makes a difference
My Question is, lets say we were designing this app to handle 2mil people ++, would it be better to have everything in 1 large database, or have each person / company have its own database and why?
ANY help given would be greatly appreciated, and thanks in advance!
My Question is, lets say we were designing this app to handle 2mil people ++, would it be better to have everything in 1 large database, or have each person / company have its own database and why?
ANY help given would be greatly appreciated, and thanks in advance!
ASKER
each user will be storing similar data - Basic contact related data and sales data. Every client will have the ability to save the exact same info as another, but can choose not to use certain portions if it doesnt fit their business model.
is that what you were looking for?
is that what you were looking for?
One database. According to Books Online the maximum number of databases per server is 32767. Even if it were possible, millions of databases would be virtually impossible to manage effectively and wouldn't give you any additional benefit if they are all on one server anyway.
However, with millions of users I expect you might be planning to have more than one SERVER in some kind of federated server farm. If so, then that is one reason why you might want more than one database. Don't have one database per user. You could segment the data so that the users are divided between 2,4 or N servers.
However, with millions of users I expect you might be planning to have more than one SERVER in some kind of federated server farm. If so, then that is one reason why you might want more than one database. Don't have one database per user. You could segment the data so that the users are divided between 2,4 or N servers.
Yes, I think you should go for one database, and sales data would simple have a foreign key to associate it with a client.
For performance, you could segment (or partition) the data into groups by region or state, but still use one table. Backing up and managing the data should be simpler if you do it this.
Imagine for a moment that you want to add a field. You would have to add a field to millions of tables, I just don't think it is feasible.
For performance, you could segment (or partition) the data into groups by region or state, but still use one table. Backing up and managing the data should be simpler if you do it this.
Imagine for a moment that you want to add a field. You would have to add a field to millions of tables, I just don't think it is feasible.
ASKER
Sounds good. and I appreciate the comments. Do you happen to have any reference that I can show my dev team?
this is a huge topic, perhaps you can get started with these links.
http://msdn.microsoft.com/en-us/library/aa198334(SQL.80).aspx
http://www.sqlteam.com/article/partitioning-the-data-in-a-table
http://www.databasejournal.com/features/mssql/article.php/3647561/Data-partitioning-in-SQL-Server-2005---Part-V.htm
http://msdn.microsoft.com/en-us/library/aa198334(SQL.80).aspx
http://www.sqlteam.com/article/partitioning-the-data-in-a-table
http://www.databasejournal.com/features/mssql/article.php/3647561/Data-partitioning-in-SQL-Server-2005---Part-V.htm
The references may not be enough. If your Dev team seriously considered the idea of having millions of database then you obviously don't have the right dev team for the job. Hire a DBA or someone with experience of developing scalable database solutions.
If you develop the system for several thousand users, the concept can be scaled up to millions. Good practices are good practices. Most likely you wont need to be able to support millions of users in the near term, so I would be most concerned with structuring your data (tables and databases) in a way that is scalable and easy to backup.
Scaling up to millions of users (now) will be costly in terms of servers and software. The staff you have now may not be adequate for such an endeavor, but perhaps they can help you get started. What you are eluding to sounds like you would need a good size crew, something akin to what yahoo has for some of their departments.
Scaling up to millions of users (now) will be costly in terms of servers and software. The staff you have now may not be adequate for such an endeavor, but perhaps they can help you get started. What you are eluding to sounds like you would need a good size crew, something akin to what yahoo has for some of their departments.
ASKER
yeah definitely starting a lot smaller now, but will grow quickly once released.
definitely the WRONG dev team, but we are committed to them at least for phase one and they were paid to build to standards and best practices and to plan for millions of users. Since i am no where NEAR an expert on the subject, i need something tangible that i can show them so that they have to redesign without charging us more.
They said they did it this way because it would be more secure, but now when reading some of your comments and such, sounds like even in 1 database, it would be secure, so they don't have anything now.
once initial dev is done we are planning to hire a good crew to maintain.
definitely the WRONG dev team, but we are committed to them at least for phase one and they were paid to build to standards and best practices and to plan for millions of users. Since i am no where NEAR an expert on the subject, i need something tangible that i can show them so that they have to redesign without charging us more.
They said they did it this way because it would be more secure, but now when reading some of your comments and such, sounds like even in 1 database, it would be secure, so they don't have anything now.
once initial dev is done we are planning to hire a good crew to maintain.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've seen situations where differently structured data is stored in XML in a table with a generic structure that way you store and retrieve more easily, but the problem here is that your searches will be slow.
Perhaps you could describe what kind of data each users needs to store?