2

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...

  • 2
    How deep are your "folders" and how big is the folder table anticipated to be? Depending on size, you could store the owner only on the root parent record and then use a recursive CTE to pick up the top parent to determine if someone has access. – indiri Sep 26 '17 at 22:20
  • 1
    You say "preferably, the owner would be the same for all folders descending from a particular root node." Unless that's one of the business rules for the system, you will have to allow every folder to have a different owner. Options may be different depending on whether that's the default assumption unless overridden; a firm business rule; or wishful thinking on your part. – RDFozz Sep 26 '17 at 22:21
  • @indiri I do plan on recursive CTEs to query the tree, but I hadn't thought of just... ignoring the owner on non-root nodes. It does, however, necessitate more complex queries (i.e. if you want to query any node, you also have to query all of its ancestors). – rintaun Sep 26 '17 at 22:57
  • @RDFozz I guess what I meant by "preferably" is that it is a business rule, which could preferably be ensured at the database level. I'm the sole designer and developer of the application in question, so I can make sure it's enforced somewhere, but I prefer data constraints to be, well, constraints on data. – rintaun Sep 27 '17 at 0:17
  • Something else to consider in your design. How likely are any of the following: Ownership of root folder changing; Need to allow multiple owners of root folder; Descendant folder being moved to a different root folder. – RDFozz Sep 27 '17 at 14:44
2

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.

  • Wow! I don't know why the check constraint thing didn't occur to me. This does simplify it a lot. – rintaun Sep 27 '17 at 22:29
1

A possibility is to split the folder table in two, treating the root folders as they were special case of folders (inheritance):

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
);

CREATE TABLE root_folders (
    folder_id INTEGER NOT NULL REFERENCES folders (id),
    user_id INTEGER NOT NULL REFERENCES users (id)
);
0

Do you need to know who is sub-folders owner User every time when you are going to query it? If yes I will suggest you to keep the ownerUserID column in your folders table and create a reference to the users table.

The advantage is faster data retrieval, so you don`t need to do multiple self-joins to understand who is your sub-folders owner user. This is standard de-normalization, when you duplicate your data, to get the data faster.

The disadvantage as you mentioned is that you need to duplicate owner user information for each sub folders row in your folders table.

0

If ownership of the folders will not change, and folders will not be moved from one root folder to another, and given you've said you want to lock down ownership of folders so whoever owns the root folder owns all the subfolders of that folder as well, you have two options.

Generally, you want to repeat data as little as possible. For that, having an owner_id on the root folders, and leaving the same blank (or even non-existent) on the child folders would be ideal. This would work very much as proposed by Evan Carroll; any folder can have a parent or an owner, but not both.

Using a CTE, you should be able to retrieve the owner of a folder if you need that every time you retrieve information about the folder itself. However, if you wind up having paths that a dozens of folders deep, and you have thousands of folders per root folder, it's possible that performance wouldn't be ideal. In that case, you might want to consider having each folder's owner_id set to the owner_id of the root folder (as suggested in Artashes Khachatryan's answer). If the root folder is added first, and the owner_id is required upon creation, then it should be possible (either in a trigger, or in some sort of job that runs extremely frequently) to have each folder without an owner_id to copy its parent's owner_id into itself.

I would be less inclined to recommend this if not for the stated unchanging nature of the folder ownership; updating the owners would be much more painful if every folder has an owner_id than if only the root folders do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.