From: Mike Beebe on
I'm creating an application (just started) with a front end (GUI) and backend
(database). The database will be populated with file related data populated
from the FileSystemWatcher. I want to discuss the database design and how it
relates to the files and their paths and how information is obtained via
FileSystemWatcher.

Since folders usually contain multiple files, I thought it would be a good
idea to have a file table and a folder table. Since file names (without
path) may not be unique, I thought using an integer as a primary key would
work well especially if the file gets renamed, all I'll have to do is update
the file name field. The records in the file table would have a secondary
key field in it referencing a folders primary key (from the folder table)
which would also be an integer. If the folder gets renamed, all I have to do
is updated the path field in the folder table.

I have noticed if a sub-folder is moved to and/or from a folder beneath the
watch root folder (I'm watching sub-directories too) or to or from other
folder structures (non watch) on the same drive that I don't receive any file
events. For example, if I move a folder off the root of the watch root
folder to be beneath another folder off the watch root folder (i.e. move it
one level deeper) then the only events that occur are one for deleting the
folder then one for creating the folder under it new parent (no file events).
This limited information (no file information) will make it difficult (I
think) update the path field for the record of the folder that got moved.
Strictly from the database's perspective, it's a rename operation because all
the files that use that folder are still pointing to it (primary folder key),
the path just needs updating.

In any case, do I have a good database design and what can I do to keep the
data in sync with each other?