[thelist] Hierarchical data in the database

Bill Moseley moseley at hank.org
Sun Jan 21 18:18:00 CST 2007

I need a little database design help.

I want to move some files that are currently on disk into the
database.  These files happen to be html templates, but they could be
anything.  The will be indexed by a path name (just like files on disk
might be).

The files are currently under revision control, so need to keep some
of those features -- that is, I want to track previous versions.

Here's one way to manage this:

    create table template (
        id              SERIAL PRIMARY KEY,
        path            text,     -- e.g.  path/to/templates/footer.html
        current         boolean,  -- true if this is the current template
        created_time    timestamp(0) with time zone not null default now(),
        update_by       integer REFERENCES person,
        CONSTRAINT      one_current_template UNIQUE( path, current )
    create index template_path ON template ( path );
    create index template_current ON template ( current );

So changes to a template are always inserted into the table (not
updated) and the "current" flag is only set on the most recently added

That's pretty easy to work with when you know the full path, but not
as nice for browsing in a hierarchical way like one can with the file
system.  I suppose to list all the templates in the /path/to/foo/
"directory" I could use LIKE on the path.

Another approach would be to have a table like this:

    create table template_node (
        id          SERIAL PRIMARY KEY
        name        text, -- i.e. "footer.html"
        parent_node int REFERENCES template_node,
        is_leaf     boolean

Which makes it easy to show all the templates and nodes in the "root"
(where parent_node IS NULL), or all the templates in the foo directory
(where parent_node = <id of foo node>).

But, then how does one access /path/to/foo/footer.html efficiently?

I could add a "path" column to template_node, but that's
de-normalizing the table too much for my tastes.  But, maybe that is
the best solution?

Or maybe I could create a table for looking up by path, but I suspect
that's the same thing as adding a "path" column.

Plus, using "template_node" then I'd need a separate history table for
tracking revisions.

I'm stumbling around a bit here.  How would you set up the tables?


Bill Moseley
moseley at hank.org

More information about the thelist mailing list