Drupal 7: Help Un-Block Fields in Core!

Dries Buytaert, the Drupal project lead, has for the past few years mentioned CCK (Content Construction Kit) fields in Drupal core as one of the "killer" features for the next version of Drupal. In addition to existing functionality which lets users create any content type (such as "announcement" or "product"), fields in core would let you add date fields, text fields, image fields, and so on to any content type.

There is currently a lot of energy around this area, but we're blocked on one very important thing: we need to nail down the database schema. See Karen's Field Structure thread for more details, a summary of which follows below.

Currently, regular old single-value fields (like "first name") are easy. Just cram 'em in the content type table. But when you start moving into more advanced areas like shared fields (such as a "picture" field that's attached to both "book" and "video game" content types), or multiple-value fields (such as a group of checkboxes for "interests"), those currently get split off into their own tables in order to normalize things. Moving fields to and from multiple value and shared has always been a harrowing process, involving table structure manipulation and data migration. However, with the abstraction brought on by the new Schema API in core, this suddenly became a lot tougher.

A couple of options have been proposed, which include a de-normalized schema per content type (leading to a bunch of NULL values for multivalue fields and data duplication for shared fields), and a "each field in its own table" approach, which involves many (sometimes MANY many) joins, which can kill performance on a large site.

So, calling all database schema experts, SQL gurus, performance nuts, etc. We need your help. We need to make sure we're not missing any obvious options that would make this work more slickly, and we also need some help benchmarking to determine what the best option is going to ultimately be. Please head over to http://groups.drupal.org/node/9297 and post your thoughts!