Reducing Number of Fields in Content Type Context:

I have a product content type on Drupal 7 with >100 fields, and there are >7000 products, all with several revisions. Our client keeps requesting that we add and remove fields, which is quite painful and takes several tries to successfully complete due to mysql having a conniption.

The go-to strategy has been to increase the RAM on the server, but we are hitting a cost wall and this bothers me as a developer since we are just deferring dealing with the problem.

Comedy Option:

The majority of these fields are specifications which are never related, filtered or displayed outside of the detail view. Could I reasonably create a JSON product_spec field that contains all of these spec fields and write custom modules to display them on the detail/spec screens, allow editing through the API, and allow editing them on the admin interface?

Is there an existing module that does something similar? Would entitycache be a much more practical option?
This is really interesting and, whereas the suggestions in the other question are really good as pointed out by the comment above, I don't really think it's practical to give up on the Field UI for this (like mentioned in the other question).

Let me explain:


Creating a custom field is really easy, there's a good example in Panopoly Widgets for the dashboard widget, which has a custom field for the Spotlight widget.
Creating a custom entity isn't so bad either but creating every single field using Form API should be out of the question


The only plausible solution that I can think of would be to implement hook_field_storage_info() and create a new Field API storage backend that uses a better table configuration.

If you look at field_sql_storage.module in core, it's a pretty concise module that provides the one field per table storage backend, I'm sure with some hacking around you could create a new backend that uses one table per entity and stores each field in a new column there (kinda like D6 CCK with cardinality 1 fields).

You would have to be constantly altering the table schema whenever new fields are added (which should be fine). This might be the place to do it: hook_field_storage_create_field().

It's pretty theoretical but I think it'll work... the beauty of this approach is that you can still use Field UI for everything as usual.

Caveat: You're gonna have to figure out how to handle complex fields or multiple cardinality (hint: more columns?)

PS - Knowing of the frequent schema changes, you wanna make sure you have backups/concurrency for peace of mind.

Good luck and if you get this working I'd like to see it! :)