Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at physical level? How many space will use many tables instead of one table? I'm thinking in how Wordpress handling the multiblog feature because I'm starting a new project and I'm looking for the best approach.

share|improve this question

4 Answers 4

up vote 7 down vote accepted

Assuming that you are talking about tables containing the same kinds of entities, you typically want to have one table.

You would not have any performance differences and a whole lot of management differences between the two approaches, with the single table being easier to manage. Typically large tables do not have performance overheads compared to smaller versions of the same when properly indexed, since the index seek time grows slowly compared to the row growth.

In a normalized design, you will have different tables for different entities or relations, but as far as partitioning (either supported with a DBMS feature or manually with separate tables), that usually is necessary when you have certain requirements, like granularity of backup or data loading/unloading on a partition basis.

share|improve this answer
    
Thanks this clarify my concept and doubts but I'll expect for some others replies to see how others think about it. Anyway you get your points. –  ReynierPM Jun 18 '12 at 17:06
1  
@ReynierPM just curious if you expect others to provide separate answers if they agree with this answer, or just up-vote this answer? –  Aaron Bertrand Jun 18 '12 at 17:27
2  
@ReynierPM If you provided more details about your requirements, you could probably get more and more detailed answers. –  Cade Roux Jun 18 '12 at 17:45

It's not clear why these two setups would be equivalent. Normally, you would store all data for one kind of entity in one table. Perhaps the "one table" is storing some type of entity and there's a field that indicates a form of variance for that entity (such as "colour" for a table that stores information on vehicles). You could create views that look like the "many tables storing few rows" that are backed by the "one table with many rows". I'm not 100% sure I understand your problem, but if I do, I think this is the normal approach to solving it.

share|improve this answer

I think the concept you mentioned: multiple tables with fewer rows and few tables with multiple rows are mapping to the trade off between normalize and de-normailize. there is no certain things like which is better, it purely depends on application scenarios.

for applications like you mentioned wordpress, there won't be that much data, so the balance between normalize and de-normalize for me I will tend to de-normalize the schema design.

share|improve this answer

WordPress has the drawback that it creates a dozen or so tables for each "user". This bloats the one database (directory) it uses. A hundred users is no problem; a thousand is risking OS sluggishness because of the number of files in a directory.

share|improve this answer
1  
Wordpress uses MySQL, right? So, you can change the default engine to InnoDB and one-file-per-table=No and the OS will only be seeing one big file. –  ypercube Jun 20 '12 at 22:45

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.