Earl Miles wrote:
At one point we had a 'field' op to nodeapi that let modules add fields to the node table. I would like to see this restored. Then we could justify never ever having a 1::1 relationship table for 'node'. This was taken out when the node_revisions table was added.
Content types adding tables with 1::1 relationships to {node_revisions} aren't a problem unless we use fields from the content type's table in WHERE or ORDER BY criteria. I'm not aware of that happening anywhere in core.
Which leads me to another question: Should the 'current' node_revision be reflected in the node table, to eliminate that join if we need to sort/query on something in that table?
We could also mark rows in {node_revisions} with an is_current field that's set to 1 for the current revision and NULL for older revisions. With a UNIQUE index on (nid, is_current), we could even have the database enforce the restriction. UNIQUE indexes ignore combinations with NULL values, so only a single row per nid could have a non-null is_current value. From the MySQL manual:
In MySQL, a UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which an indexed column allows only a single NULL.
The UNIQUE index on (nid, is_current) would allow rapid lookups of a given nid's current revision. That said, the current relationship between {node} and {node_revisions} isn't too bad. We tend to not put WHERE or ORDER BY criteria on {node_revisions} when joining.