Expert opinions on a very large number of columns in a mySQL table?

Question

I am creating a tool with which to edit web pages within a CMS. The main goal of the tool is total flexibility for the user. Therefore, a great number of properties can be edited in it - properties like these (snippet):

langbutton_menu_border_color_left 
langbutton_menu_border_width_left
langbutton_menu_border_style_left 
langbutton_menu_border_color_right

... you get the drift. To date, I have 238 such properties, mostly integers and short strings. I have now to create a mysql table for the data. I have some years of web development experience, and it was an absolute taboo to even consider putting 238 columns into a mySQL table. But on second thought, I'm starting to think, why not?

It is the most convenient thing for me right now, as my CMS I am integrating this new tool in has a collection of ready-made input elements that are connected with single database columns. Any other way of storing the properties (e.g. grouping them so a "border" property is stored in one field) would require huge changes to the collection, which I would very much like to avoid - I am in a big project and literally working day and night.

I would create and alter the table based on a XML definition, so I could live with administering a 238 column table. Storage efficiency is not important - the expected number of pages will not exceed 50-100. I need to make no queries on the table except for loading a single page at a time using the primary key.

So, mySQL experts, is there anything seriously speaking against storing this kind of data in 238 columns? Would you expect problems, exponential memory usage, anything like that?

Usually, I would translate the various properties into full CSS strings, and build classes that can parse and deal with such strings - that would reduce the number greatly. But considering the time constraints?

Answer 1

How many of these columns need to be queried and updated independently of the others? Are any of these columns involved in relations to other tables, or are they all just data?

I don't know your usage plan, but in some cases it makes more sense to store all of this data as a BLOB. That's what I would do if it will always be retreived together and will not take part in any query.

Answer 2

In theory, mySQL now is limited to 4096 columns in a table (a little bit less considering other constraints, i.e. default NULL values etc.). So, you have a quite big margin. Personally, in a web dev i try to keep # of columns < 50. I saw tables with 100+ columns, and it worked, but it's very hard to maintain such tables. If you don't have to search on that columns, consider serialize on php array and store values in TEXT. It's faster and more flexible.

Answer 3

I've tried an aproach like this before. Chances are nobody is ever going to use all 238 properties on a single element.

IMHO you would be better off suggesting the available properties to the user. Then let the user combine the available properties by picking them out of a list and setting their values. You could then combine the users input into a text array like [property:value,property:value] and stick this into a single column on the element you need styled.