Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS SQL 2008 database design for public site

Posted on 2009-04-07
13
285 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

839 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