Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This is semi-hypothetical, and as I've no experience in dealing with massive database tables, I have no idea if this is horrible for some reason. On to the situation:

Imagine a web based application - lets say accounting software - which has 20,000 clients and each client has 1000+ entries in a table. That's 20 million rows which I know can certainly slow down complex queries.

In a case like this, does it make more sense to create a new table in the database for each client? How do databases react to having 20k (or more!) tables?

share|improve this question

5 Answers 5

up vote 15 down vote accepted

Generally speaking, no, it makes no sense to have a table (I think you actually mean database here) per customer. 20 million rows is relatively small for a database table. Query speed against that should not be an issue so long as the database is properly tuned (indexed) and the queries are put together correctly. Whatever benefit you think you'd get from having them separate would be offset by the additional complexity of managing 20,000 individual databases. For ex, what happens when you want to change the table structure? You now have to do it 20,000 times!

Worse case, if you eventually do find that the database size is becoming an issue, you can always splt them into separate databases afterwards.

share|improve this answer
    
no, I literally meant tables within the database. I can't imagine a reason to create a database per client. And if 20 million rows is small, whats large? And what do you do at that point? –  Will Dec 28 '10 at 19:43
1  
@ChrisF, exactly - there's a lot of cases where the technology or business model calls for separate DB's per client. But I cant think of a reason for separate tables within the same DB. –  GrandmasterB Dec 28 '10 at 19:50
1  
@GrandmasterB - I think @Will is asking the wrong question. –  ChrisF Dec 28 '10 at 19:51
1  
@Will: If possible, go to an Oracle User Group meeting, or the equivalent for some other high-end database. You'll find that your ideas of "small" and "large" need a lot of readjustment. It happened to me. Hint: if it fits on one disk, it isn't large by DBA standards. –  David Thornley Dec 28 '10 at 20:51
1  
@Gorton, InnoDB is generally considered better for reliability and concurrancy, MyISAM for speed. So you really need to evaluate the different storage engines based on your specific application's expected database usage. –  GrandmasterB Dec 28 '10 at 21:40

Sounds like a bad idea.

Don't try to outsmart the database with exotic constructions like this. Database engines are designed with lots of optimizations to handle large data sets. For example, what you are describing sounds awfully close to an attempt for manually implementing indexes. Just use indexes provided by the DB Engine, they are implemented much better than you are likely going to be able to do on your own, and it won't require as much maintenance.

Also, as a general rule of thumb. I suggest not architecting a database in a manner that requires manipulation or creation of database structures (tables,fields) during the normal use of the application. It makes optimizing for performance a bear and often forces you to give too many permissions to users to do routine tasks potentially creating security holes.

share|improve this answer
    
I'd upvote once for each of your two paragraphs if allowed. –  David Thornley Dec 28 '10 at 23:05
1  
+1 for advocating good, simple design techniques! –  David Dec 29 '10 at 3:19

Here's an article I always urge people to read, when they ask this question:

http://datacharmer.blogspot.com/2009/03/normalization-and-smoking.html

share|improve this answer
    
I had no idea that a DB creates an actual file per table =x –  Will Dec 28 '10 at 19:49
1  
This might depend on actual RDBMS used. MySQL does that (up to three files per table if you use MyISAM). Others might not. –  Mchl Dec 28 '10 at 19:52
    
Enterprise version of SQL Server will do it if you design it that way, but not automatically. –  JeffO Dec 28 '10 at 19:54
    
Oracle definitely doesn't do that. –  user281377 Dec 28 '10 at 20:35
    
Oracle can do it, the same way that SQL Server can do it, but I can't imagine why you'd ever design your schema to have one file per table. Splitting a database into multiple files makes does sense, but not one file per table. –  Dean Harding Dec 29 '10 at 2:54

IMHO a single table shouldn't be a problem, so don't create an issue where one doesn't exist - yet. There is a lot you can do to help performance. You can partition a single table to multiple files based on clientID or a date field to help with IO. Your db doesn't have to keep track, optimize and cache 20,000 different sql statements for every query you're site will need. You can index by clientid. 20K clients can pay for a lot of hardware.

For this type of table, a NoSQL type db could be used.

With 20K clients, the database may not be your weakest link, so why introduce this much complexity?

share|improve this answer
    
` You can partition a single table to multiple files based on clientID or a date field to help with IO.` -- not sure what you mean by this. Any clarification? –  Will Dec 28 '10 at 19:44
    
Multiple files on the operating system. A server can do more read/writes to many files instead of just one. –  JeffO Dec 28 '10 at 19:52
    
I guess I meant: I've never heard of such a thing, where do I find more information on doing this? :-) But I'll hit up google ~ –  Will Dec 28 '10 at 19:53
    
msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx You can run into backup performance issues if indexes are on separate files than the tables they index (or maybe drives?). –  JeffO Dec 28 '10 at 20:07

That's really bad approach.

Partition the table vertically, 2 database servers one for odd user ids, and another for even should work well (the data is not related between users).

Sort the data by user_id and if that's not possible get a huge amount of RAM or SSD disks.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.