0

I have a 30000 by 2000 matrix that I need to put in a table. However I can't have 2000 attributes in one table as it would exceed the storage limit. Is splitting these attributes into different tables considered good practice. If not what other methods are there to deal with my situation ?

Thanks,

jkjk
  • 5,777
  • 4
  • 20
  • 19

2 Answers2

2

You could investigate an EAV model. It often gets a bad review, but I find there are some instances where it is quite an effective method. There was a great post on SO relating EAV to pure 6NF and how it was a great model however, I can't seem to track it down.

Having said that, it is most useful when trying to store a wide variety of attributes against a single entity. This may not be your primary objective, and therefore it may be that others have a better idea, but I'd say it is worth taking a look.

Edit:

Here is the link I was referring to earlier It's worth investigating some of the points raised here to determine if it is actually suitable to your situation.

Community
  • 1
  • 1
Mr Moose
  • 5,946
  • 7
  • 34
  • 69
  • EAV is a nice solution if a little hard to read from a database level, I suppose you could then create views based on the EAV structure to make the data easier to read during development. – Jimbo Mar 12 '13 at 10:16
0

There's no reason why you shouldn't look at splitting this into multiple tables. Such a strategy is called partitioning. http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html

hafichuk
  • 10,351
  • 10
  • 38
  • 53
  • I don't think partitioning means refactoring your database to use different tables, I think it means single tables using different files in the mysql data storage to allow performance improvement in some cases. Partitioning isn't the solution to this issue, refactoring is. I hope you don't mind me correcting the definitions but it's quite a big distinction as I understand it. Having said that if splitting the data across multiple related tables is what you meant then that's a good solution. For structures subject to frequent change the EAV solution posted by Mr Moose would be good. – Jimbo Mar 12 '13 at 10:14