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