0

I have a lot of databases with relatively large amounts of columns ranging from 5 to 300. Each table has at least 50,000 rows in it.

What is the most effective way to store this data? Presently the data has just been dumped into an indexed sql database.

It was suggested to me to create 3 columns as follows.

Column Name, Column category, Row ID, Row Data.

example data would be Male, 25-40, 145897, 365

Would this be faster? Would this be slower? Is there better ways to store such large and bulky databases?

I will almost never be updating or changing data. It simply be outputted to a 'datatables' dynamic table where it will be sorted, limited and ect. The category column will be used to break up the columns on the table.

user1641165
  • 436
  • 2
  • 4
  • 17
  • This is a tough question to answer without knowing what kind of data is in those 5-300 columns, how it is queried and used. Can you give some more details? – Peter Sep 04 '13 at 16:25
  • 1
    It depends upon the type of the columns and queries – user4035 Sep 04 '13 at 16:25
  • 2
    It sounds like you're describing a an entity-attribute-value model. You can google for that term and get some info on the pros and cons of it. Also, check out this question: http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question – austin Sep 04 '13 at 16:27
  • 2
    A dumb rule I ussually follow is: "It is better a long strip than a wide sheet". Just like Smith's answer says, you need to normalize your data. The specific needs of your database will help you decide the best storage strategy. – Barranka Sep 04 '13 at 16:30
  • To be quite honest, the data is pretty much just going to be queried in it's raw form. However saying that, by doing it the latter way, I am able to add a category column to categorize the individual data columns. (Kinda nice to categorize 300 columns) – user1641165 Sep 04 '13 at 16:36

1 Answers1

5

Normalize your db!

I have struggled with this "theory" for a long time and experience has proven that if you can normalize data across multiple tables it is better and performance will not suffer.

Do Not try to put all the data in one row with hundreds of columns. Not because of performance but because of development ease.

Learn More here

Smith Smithy
  • 585
  • 6
  • 24