The Database
October 9, 2000
The key to making this system work is to store everything properly
in a
database (when is a database not the
solution?). You could simply
store each individual file's properties in a separate row in a
table, but you lose a lot of hierarchical functionality that
is present in file-based systems, not to mention you might run
into data duplication errors down the road. The best way to implement
the source control system would be to build a hierarchical file structure
similar to the one that is already there.
We'll try to keep this simple, but we'll need at least two database
tables:
|
tblFiles |
|
tblDirectories |
|
FID |
Unique ID to keep track of records |
Autonumber |
|
DID |
Unique ID to keep track of records |
Autonumber |
|
Name |
File name |
String |
|
Name |
Directory name |
String |
|
ParentFolderID |
To which folder does this file belong (foreign key to tblFiles) |
Integer |
|
ParentFolderID |
To which folder does this file belong |
Integer |
| User |
Who currently has this file checked out? (foreign key to tblUsers) |
Integer |
|
|
|
|
|
LastModified |
This is the last modified date of the file |
Date/time |
|
|
|
|
|
Location |
Physical location of the file |
String |
|
|
|
|
And of course, a user table:
|
tblUsers |
|
UID |
Unique ID to keep track of records |
Autonumber |
|
Name |
Name of user |
Text |
| |
|
NOTE:
You can use a pre-existing user table if you wish. We simply
provide the tblUsers table definition for completeness
sake.
|
With this database architecture, you are nearly unlimited in the
depth of your hierarchy tree. Each folder and file has a parent
folder, and the topmost folder should have a ParentFolderID of zero.
Note that the database information does not have to reflect the
actual file system hierarchy at all; you could make the structure up if
you wish, use the actual structure, or start anywhere in the middle.
This is very helpful, as most of the time, we won't want to put
source control on an entire file system, but rather only a few
directories.
For tblFiles, we don't necessarily need to keep track of a large amount
of information. The most important items are the date the file
was last modified, the filename, and which user has currently checked
the file out, if anyone at all. We will not want to make the filename a
unique key, because you may have multiple files with the same
name in different directories. Each row, however, should be unique.
Source Control Defined
Effective Source Control
The Front End
|
