I’ll give 500,000 points to anyone who can come up with a database table design to handle the following functionality in a clean way.
I need to implement a filesystem that consists of both directories and files. The directories should be limitlessly nestable. There should be only one root directory. Each item should have a name, a creation date, and a last modified date. Additionally, each file item should have a mime-type, and, of course, possibly binary data. Unix style symbolic links should be implemented. Additionally, Windows NT/2000 style permissions should be implemented (minus the security holes… thanks). For any given “path” there should be one (and only one) file (or directory) that is associated. Items should be reachable by “path”. Additionally, it should be fairly easy to list all the items in a “directory”. It should also be fairly easy to list all files that have been changed recently, either for the entire “file system” or for a single “directory”. The system should be capable of holding in excess of 50,000 items.
Extra points will be awarded if the data is searchable in someway. Even more extra points will be awarded if the directories and files can have arbitrary meta-data associated with them.
Aside from these requirements, you can design the tables in anyway that you desire. They can be normalized, un-normalized, and/or optimized. You can use one table, or fifty. If needed, you can use MySQL specific extensions… but only if it is the only way. In fact, you can even NOT use a database, as long as it is fast with 50,000 items in it.
Thanks in advance!