?
Solved

MS SQL 2008 database design for public site

Posted on 2009-04-07
13
Medium Priority
?
303 Views
Last Modified: 2012-05-06
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!
0
Comment
Question by:frettfreak
  • 5
  • 3
  • 3
11 Comments
 
LVL 5

Expert Comment

by:mrcoolcoder
ID: 24092847
Will each users database be structured differently.  I find that you need to standardize your content if it is possible, because backing up the database could be a nightmare.

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?
0
 

Author Comment

by:frettfreak
ID: 24093141
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?
0
 
LVL 22

Expert Comment

by:dportas
ID: 24094350
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.



0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 5

Expert Comment

by:mrcoolcoder
ID: 24098706
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.
0
 

Author Comment

by:frettfreak
ID: 24102520
Sounds good. and I appreciate the comments. Do you happen to have any reference that I can show my dev team?
0
 
LVL 22

Expert Comment

by:dportas
ID: 24104526
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.
0
 
LVL 5

Expert Comment

by:mrcoolcoder
ID: 24108345
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.
0
 

Author Comment

by:frettfreak
ID: 24111645
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.
0
 
LVL 5

Accepted Solution

by:
mrcoolcoder earned 1000 total points
ID: 24111722
Sometimes people get bogged down in the details without understanding what to really emphasize.  I think that if they can get a prototype working with a few databases, its not the end of the world.  You can always migrate your data into a larger database and combine it that way.  You are not at a dead end yet.  It is not as difficult as it sounds to scale up.  So read up an all you can about scaling up and partitioning but in the mean time, you should be most concerned with development that meets current expectations and requirements.
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 1000 total points
ID: 24114131
>> i need something tangible that i can show them so that they have to redesign

Books Online
http://msdn.microsoft.com/en-us/library/ms143432.aspx
Maximum Databases per instance of SQL Server: 32,767

So using their solution you are limited to that number of users, which doesn't meet your requirement to support millions. I've never actually tried creating that many databases but I suspect they haven't either. Their security argument is nonsense. By the way, where is the DBA who will manage this system once it is live? That person ought to have something to say about this.

What I suggest is that you hire someone independent to review the work of the dev team and perhaps manage them as well. Do they have their own Project Manager? You are surely going to need someone else on your side to make sure they deliver.


0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question