Still stuck... er, I mean... visiting ;) Boston after Drupalcon, thanks to a lovely winter storm in Canada that refuses to go away. Karen Stevenson was nice enough to put me up in her hotel room for a night, so I wanted to pay her back by making this important issue my "itch of the week": we need to figure out a data model in order to move CCK fields in core efforts forward. See Karen's Field Structure thread for more details.
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!