The naive way would be to put the whole path into the DB as a string and it would work for toy DBs. However, this approach have a couple of flaws. For example, say I have 100K files under /var/www/sites/ then storing /var/www/sites 100K times in the DB is very inefficient. I am sure there is a much better way to do this.
I would like to index only the file paths on a DVD and later search for mp3 files or for directories, etc. The preferred RDBMS is SQLite (perhaps FTS Tables?). My goal is to learn, I know there are a bunch of desktop search engines for this.
This problem has a name: storing hierarchical data and has well-estabilished solutions
Managing Hierarchical Data in MySQL
One more Nested Intervals vs. Adjacency List comparison
How to store directory / hierarchy / tree structure in the database?
Optimized SQL for tree structures