Thanks for this valuable overview!

If, however, a field is shared among more than one content type, a table called node_data_field_X, where X is the name of the field, is also created

The node_data_field_X table is also created when the field is of type "Multiple values". This happens even if the field is not shared among content types.

When the field is multiple-valued, the node_data_field_X table contains an additional column: delta.

I think you have an error in the diagram for the node_data_field_X table. The picture shows two fields in that table: field1 and field2. However, one node_data_field_X table holds data for one field only, so you need to remove "field2" from this table. (And, you must rename the remaining "field1" to "field3", or whatever, because "field1" already exists in node_content_X.)

(BTW, what software did you use to create this beautiful diagram?)

(You should add the _value suffix to the appropriate column names in your diagram.)

Have you ever wondered about the "_value" suffix in column names?

A field's data may actually occupy more than one DB column. It's true that the simple Text and Number fields occupy only one column (field_X_value), but the more complex Date field, for example, occupies two DB columns: field_X_value and field_X_timezone.

There's nothing "holy" in a _value suffix:

Let's say I want to create a new field type: Color. This field will occupy three DB columns (for the red, green and blue intensities). I'll define my hook_field_settings as:

function color_field_settings($op, ...) {
  switch($op) {
    ...
    case 'database columns':
      return array(
        'red'   => array('type' => 'integer', ...),
        'green' => array('type' => 'integer', ...),
        'blue'  => array('type' => 'integer', ...)
      );
    ...
  }
}

Now, if I add such field, let's call it "favorite color", to my content type, my content type table would contain three new columns: field_favorite_color_red, field_favorite_color_blue and field_favorite_color_green. It would not contain field_favorite_color_value, because such a column ("value") was not defined in color_field_settings.

Similarly, the nodereference field doesn't define a "value" column. It defines "nid" instead:

function nodereference_field_settings($op, $field) {
  switch($op) {
    ...
    case 'database columns':
      $columns = array(
        'nid' => array('type' => 'int', 'not null' => TRUE, 'default' => '0'),
      );
      return $columns;
    ...
  }
}

A "value" column is defined in number_field_settings and text_field_settings.

BTW, contrary to what I've said, the Text field is not so simple: it may occupy two DB columns if an input format may be applied to the string (that is, when the "Filtered text" option is selected).

(BTW #2, in my previous comment I said you should leave only one field (in CCK terms) in the diagram for the node_data_field_X table. But this one field may occupy several DB columns (depending on its type and settings)).

I wanted to convert some simple node types to cck to take advantage of the ability to add more fields to them. I had a couple custom types I created by copying the story.module that I wanted to convert, plus I wanted to make my basic event type into a cck event type and my basic og type into a cck type. I wanted to do all this without losing any of the data that existed. I thought I'd share the process and a php snippet that worked for me to do this. This should work for any simple content types (i.e. changing types that do not use revisions which only have a title and a body to a cck type with a single description field that will hold the body content).

1. First, create the new cck content type you want to move to (in this example, it ended up named 'content_event')
2. CCK doesn't use the body field, so you need to add a cck text field for the body content. Give the field more than 1 line to make it a textarea instead of a textfield, and be sure to indicate that it uses filtering. Note the name of the field that is created. For my example, I created a field called 'field_description'.
3. Create a test node to be sure that everything got set up correctly and that you can manually enter data into your new content type and that it gets saved correctly. Be sure that you get a textarea field instead of a textfield and that the filter is available for selection.
4. Make a good backup of your database. Go ahead and make another one just in case :-)
5. Now run the following code to change the data in the database.

Note: I can see that the code gets cut off when posted. I tried several methods of posting it and none displayed correctly, so if it's getting cut off, just pick it up from the source of this page.

  // convert another node type to a cck type
  $from_type = 'event';
  $to_type = 'content_event';

  // the name of the cck field that will hold the value of the node body
  $to_body_field = 'field_description';

  // pull up nodes with the old content type
  $results = db_query("SELECT nid, title FROM node where type='$from_type' ORDER BY nid");
  while ($arr = db_fetch_array($results)) {
    $nid = $arr['nid'];
    
    drupal_set_message('Converting '.$arr['nid'].': '.$arr['title']);

    // create a cck record for this node and make sure it's not a duplicate
    db_query("DELETE FROM node_".$to_type." WHERE nid=$nid");
    db_query("INSERT INTO node_".$to_type." SET nid=$nid, vid=$nid");
    
    // change the node type in the node table
    db_query("UPDATE node SET type='$to_type' WHERE nid=$nid");
    // move the value in node body to the cck body field
    if ($to_body_field) {
      $node = node_load($nid);
      $results2 = db_query("SELECT format, body FROM node_revisions WHERE nid=$nid");
      while ($arr2 = db_fetch_array($results2)) {
        // if there is a body value save it to new field
        if ($arr2['body'] > '') {
          $node->$to_body_field = array(
              0 => array(
                'value' => $arr2['body'],
                'format' => $arr2['format'],
                ),
              );
          node_save($node);
          $node = node_load($nid);
          $value = $node->$to_body_field;
          if ($value[0]['value'] == $arr2['body'] && $value[0]['value'] > '') {
            // if successful, erase the body and teaser (cck doesn't use them)
            db_query("UPDATE node_revisions SET body='', teaser='' WHERE nid=$nid");
          }
        }
      }
    }
  }
  // update other tables that use content type name
  db_query("UPDATE vocabulary_node_types SET type='$to_type' WHERE type='$from_type'");

Can someone with more intimite knowledge of CCK shine some light on this 'multiple' thing?
* why is it there?
* what is it for?
* how should developers handle this when storing their own data in private tables?

---
Professional | Personal
| Sympal: Development and Hosting

In Drupal 5 node_content_X was named to content_type_X and node_data_field_X into content_field_X