[Dbix-class] Re: flexible attributes on db objects

[A. Pagaltzis]

* Brad Bowman <list at bereft.net> [2007-06-13 05:30]:
> At least one project I'm working on is likely to need some kind
> of versioning and I'd like to know more about your approach.

Well, it is really specific to my case, where the “document”
table basically has nothing but an ID column; *all* of the actual
data lives in other tables.

What I have (off the top of my head) looks like this:

    document (
        id,
    )

    revision (
        id autoincrement,
        doc_id references document(id),
        num,
    )

    text_value (
        id,
        content,
    )

    text_attribute (
        doc_id references document(id),
        type_id references type(id),
        value_id references text_value(id),
        from_rev,
        until_rev,
    )

And then there is a whole array of tables and join tables like
the text_value/text_attribute pair for various data types (not
DB-centric data types, but app-centric ones; eg. several tables
with TEXT columns, where in one of them there is inline HTML, in
another there are full HTML documents, in another there are just
plaintext labels, etc.).

This way, I can track the version history of each attribute, and
independently I can also track how the collection of attributes
that constitutes a document has changed over time.

I’ve omitted the types table here; it enumerates things like
“simple document title”, “simple document body”, “simple document
author” etc, and assigns an application data type for each of
these (the title is a plaintext label, the body is a full HTML
document, the author is a reference to a user document, etc).

The ID column on the revision table isn’t actually necessary for
the schema, btw, it’s just there to ensure atomicity of revision
creation. The revision number in `num` is equal to the number of
rows with the same `doc_id` but smaller or equal `id`.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>