Solved

MS SQL 2008 database design for public site

Posted on 2009-04-07
13
279 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
13 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 5

Expert Comment

by:mrcoolcoder
ID: 24102589
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 250 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 250 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now