I have a php/mysql application. There are users who have folders. Folders can only be one level deep. Each folder can have unlimited number of documents. The relevant tables are as follows:
user_table:
user_id(PK), name, password, etc
folder_table:
folder_id(PK), name, user_id(FK into user_table)
document_table
document_id(PK), user_id(FK), folder_id(FK), date, name, etc
All queries to operate on folders and documents are of the form
select/update/insert <blah, blah, bhah>
where user_id = %d
Now, I'm looking to allow users to share they folders with other users. I want to do this in the least disruptive way possible. I also want to minimize run time overheads of resulting WHERE clause.
I've been wracking my brains and googling but haven't found a simple enough solution. I can think of implementing it in a manner similar to unix's (read, write, execute) along with (owner, group, others) mechanism. But it seems a bit too complicated for my humble system.
I would really appreciate if someone could give me pointers as to which approach I could possibly take. My goals are simple:
- The system doesn't require a whole lot of reworking
- At a minimum users should be able to share folders with other users or groups (I will add concept of groups if needed)
- There should not be too much overhead in SQL statement's WHERE
clause as opposed to what it is now (as simple as:
WHERE user_id = %d
)