When we add new fields to Drupal Content type, CCK would create a new table inside the database as content_type_<content_type_name>. Example:-

mysql> desc content_type_tweet;
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| vid                | int(10) unsigned | NO   | PRI | 0       |       | 
| nid                | int(10) unsigned | NO   | MUL | 0       |       | 
| field_follow_value | longtext         | YES  |     | NULL    |       | 
+--------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The table basically store the reference to nid and vid of that node + the value for that field. Any new fields added to this content type through will be added as a new column into this table. The column naming is fixed and you can’t customize it, CCK would always create it as field_<field_name>_value. Things look so simple so far. It look cools and allow us to add new field on the fly and have it as a new column in the content type table. But it’s not.

CCK has a concept of shared field and because all the fields other properties such as data type, length, widget etc are kept track in one master table content_node_field, you can’t have two field with a same name in different content type. So following the previous example, it’s not possible to have another column follow in another content type although both field carry the same meaning. If you insert the same name when adding the new fields, CCK will automatically append _0 to the field name.

To use similar field name in seperate content type, you have to use what CCK called a shared field. When adding new field through CCK UI, we have an option whether to create a new field or use one of the existing fields. Remember, CCK kept tracks all those fields we added so far in content_node_field table so it know what fields already exist. But when we start using shared field, CCK drastically change the structure of our table. Take the following example:-

mysql> desc content_type_tweet;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| vid   | int(10) unsigned | NO   | PRI | 0       |       | 
| nid   | int(10) unsigned | NO   | MUL | 0       |       | 
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

The table now only store a reference to node id and revision id of that node. Where they store the value now ? The answer is in another seperate table named as content_field_<field_name> as below:-

mysql> desc content_field_follow;
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| vid                | int(10) unsigned | NO   | PRI | 0       |       | 
| nid                | int(10) unsigned | NO   | MUL | 0       |       | 
| field_follow_value | longtext         | YES  |     | NULL    |       | 
+--------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

That’s all that I can digest so far. It should be more.