Representing ownership of heirarchical tree structure

database-designforeign keytree

In an application I'm developing, I have (among others) two structures: Users and Folders.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE folders (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES folders (id),
    name TEXT NOT NULL
);

My problem is that I want each folder tree to have exactly one user "owner", and preferably, the owner would be the same for all folders descending from a particular root node (i.e. WHERE parent_id IS NULL). I would like to maintain referential integrity in the database.

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be… difficult. (Maybe a foreign key on (parent_id, owner_id)? That seems… a bit gross.)

Is there some way to do this that I'm not thinking of? I am open to using a different tree structure than an adjacency list, but I'm not sure that others have any additional benefit in this case.

Note: This is a simplified version of my actual use case, but the design is the problem, not the specifics. Similarly, I am using PostgreSQL, if that matters, but I doubt that it will…

Best Answer

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be... difficult. (Maybe a foreign key on (parent_id, owner_id)? That seems... a bit gross.)

That's not true. PostgreSQL provides for CHECK constraints which make this really easy.

CREATE TABLE users (
    id   serial PRIMARY KEY,
    name text   NOT NULL
);

CREATE TABLE folders (
    id        serial PRIMARY KEY,
    parent_id int    REFERENCES folders,
    owner_id  int,
    name      text   NOT NULL,
    CHECK (
      (parent_id IS NULL AND owner_id IS NOT NULL)
      OR (parent_id IS NOT NULL AND owner_id IS NULL)
    )
);

INSERT INTO users(id,name) VALUES
  (1, 'Evan Carroll');

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (1, null, null, '/usr');
ERROR:  new row for relation "folders" violates check constraint "folders_check"
DETAIL:  Failing row contains (1, null, null, /usr).

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (1, null, 1, '/usr');
INSERT 0 1

INSERT INTO folders(id,parent_id,owner_id,name) VALUES
  (2, 1, null, '/usr');
INSERT 0 1

As for the space concern, that's hardly something I would worry about. It's 4 bytes on a row. Joining for that and establishing a 1:1 relationship sounds utterly silly.

Related Question