With the general goals introduced, we can now shift our
attention to the database design.
Note that here I assumes that you are familiar with SQL
Server 2005 Express Edition and how to create basic SQL clauses. To follow
along with the examples, you had better download the source files accompanying
this article.
In this article we will design a database named WebDiskDB, which contains two tables—Directory
for file folders and Url for file attributes,
respectively.
Now, let us look at the table structures for table Directory and Url in Table 1 and
Table 2, respectively.
Table 1: Structure for table Directory
Field name
|
Type
|
Notes
|
DirID
|
int
|
Primary Key
|
Name
|
varchar(50)
|
The name of the file/folder
|
ParentID
|
int
|
ID of the parent folder
|
Contain
|
int
|
The whole size of the file/folder
|
FileCount
|
int
|
The total file number it contains(0 - if it's a file)
|
DirCount
|
int
|
The total folder number it contains(0 - if it's a file)
|
Flag
|
bit
|
Marking whether it is a file or a folder
|
CreateDate
|
datetime
|
Creating time
|
Table 2: Structure for table Url
Field name
|
Type
|
Notes
|
UrlID
|
int
|
ID(Primary Key)
|
Url
|
varchar(255)
|
linking address
|
Type
|
varchar(200)
|
type
|
DirID
|
int
|
The corresponding DirID in table Directory(foreign
key)
|
VisitDate
|
datetime
|
Creating time
|