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 have Column A and Column B in a single table.

I need to add 1,000,000 records to this table.

I have 2 options:

  1. Keep 2 columns and add 1,000,000 records
  2. Add more columns and flatten out the data. This will decrease the row count by half.

Am I correct in assuming the table size will be the same for both options?

share|improve this question
1  
The answer depends on the data types / length. Try this MSDN for how to estimate table sizes. –  Eric Higgins Sep 14 '12 at 23:46
2  
If you need to store 2'000'000 items of data - it really doesn't make much difference whether you have 2 columns x 1 mio. rows or 1000 columns x 2000 rows .... it's just 2 mio. items of data in the end..... –  marc_s Sep 15 '12 at 0:46
    
@marc_s yes, but often "flattening" is normalisation, which ends up removing significant amounts of data. –  FreshPhilOfSO Sep 15 '12 at 3:03
    
Could you post the entire table structure for both the 2-column table and the more-than-2-column table? –  Henrique Ordine Sep 15 '12 at 9:37
7  
Your question is confusing and cannot be answered. Why would you want to split up data into more columns (assuming your data does consist only of two columns)? Adding rows to a table is something completely different than adding columns. Please add some sample data where you think "flatten out" makes sense (I'm pretty sure it won't) –  a_horse_with_no_name Sep 15 '12 at 14:04
show 1 more comment

1 Answer

The fact is that this question cannot be answered as such, so I will reframe it and answer as reframed.

The basic problem is that the database size will depend entirely on implementation details of the db. It could be the same size, could be more, could be less. We can't say. Additionally it may depend on how much repetition is avoided using one approach or the other.

At any rate it is exactly the wrong question to ask here. The right question is how your data relates to eachother. You basically have three options:

  1. Second table with 1000000 rows. Use this if there are normalization issues or if you need to break this off for table space reasons. It may increase the size of the db because of join conditions.

  2. New column. Use this if the data does not belong in the same column of the other table due to semantic reasons, but is functionally dependent on the data in the existing rows.

  3. New rows. Use this if the data belongs in the existing rows based on the internal semantics of the data.

Note that table size is not your biggest concern. You can probably use indexes to address them but 1-2 million rows is still way too small to worry about size-wise in this way.

share|improve this answer
    
Yep. If you're talking about just a couple of narrow columns, in some platforms the row overhead will dominate the space needed for storage. –  Mike Sherrill 'Cat Recall' Feb 11 '13 at 15:37
add comment

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.