Database Schema: How the relationship can be designed between user, file and folder?

Question

I was wondering how the database schema of Dropbox would be designed? I am thinking about three tables: User, File, Folder with following constraints.

Constraints:

1. A user can contain multiple files and folders.
2. Each file can be shared among multiple users.
3. A file can be organized in folder for one user but not for another user(if the file is shared).
4. Each folder can contain multiple files as well as other folders.

How would you create a relationship between them?

Answer 1

This ERD fits the rules you've described:

Note that in this schema, every user has their own individualized view of folders. Files, on the other hand, are represented only once but can be assigned to one or more folders, even for different users.

---

EDIT: (slightly) expanded model:

With the addition of a single table to track physical folders and their contents, my proposed schema also addresses all of the additional functionality suggested by Branko Dimitrijevic:

Answer 2

The Joel Brown's model is a good start, but let me tickle your imagination with another possibility:

This model has the following properties:

• It emulates file system hard links. You can have a single file in multiple folders or even multiple times in the same folder, under different names. Ditto for folders.
• It separates naming from content, which is necessary for the "hard links" paradigm. So, names are in FILE_IN_FOLDER and FOLDER_IN_FOLDER (instead of in FILE and FOLDER).
• It properly enforces the "local" name uniqueness (you can't have two files or folders named the same way under the same parent folder).
• A user owns neither files nor folders. User just owns links. This way, each user has her own private "directory tree", potentially sharing all files, or none or anything in between.

The question, of course, is whether the increased flexibility you get with this model is worth the extra complexity, but this is something only you can answer...