[development] One-to-one tables considered harmful

[David Strauss]

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.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 186 bytes
Desc: OpenPGP digital signature
Url : http://lists.drupal.org/pipermail/development/attachments/20070605/c1593b15/attachment.pgp