Companies
This table is used to store client company information and is thus one of the central tables used by the contact management module. Only 11 fields have been created, although in a production environment there will undoubtedly be a need for more.

Contacts
This table is used for storing personal contact information. Contacts have an optional "companyId field" which is used to associate them with a company. There is similarly a "contactId field" in the company table which is used to identify a person as the primary contact for an organisation.

This topology may not work in all situations, in which case you may need to consider another table for mapping contacts to companies and, possibly, projects as well. Note also that to keep the database as simple as possible we have used the contacts table to store employee information as well. To manage this, a Boolean value "isEmployee" is included in the table structure.

Bookings
This is a simple table that stores start and finish times for resource bookings. Contact and resource ids are used to map bookings appropriately.

Resources
The resources table stores the name of any bookable resource such as a meeting room or notebook computer, for example.

Projects
This is the core table for the project management module. Project records contain a name, a status, and notes. The clientId is used to map a company to the project, and the contacts table is used for both the client contact person and the employee associations.

This is the bare bones project management module included with this tutorial.

Status
This table is used to store status types such as "pending", "on hold", "awaiting approval", "checked out", and "in progress".

For more sophisticated applications it may be necessary to differentiate between status types used for documents and projects, with the latter incorporating a progressive sequence of some kind.

Documents
The table documents that stores the bulk of the information used by the document management module. Here we are storing the file name and location of the documents as opposed to using a BLOB field in the database.

This methodology will require some additional programming to ensure that the file system and database records remain synchronised.

The Time Tracker Sample project available from the ASP.NET site can be used as the foundation of an entire Intranet.

DocumentTypes
This table stores the list of valid file types for the document library with information such as MIME type and acceptable file extensions.

Associations
We decided to use a single table to store associations between database objects, although some implementations may require separate tables for each type.

For instance, the following fields are all referenced by foreign keys that are not enforced: companyId, projectId, contactId, resourceId, and documentId.

On the upside, this means that complex relationships can easily be managed between the tables referenced by these fields. The disadvantage, however, is that SQL queries can become diffi cult to manage as the table inherently stores a variety of different types of data.

Tasks
This table stores tasks that are associated with projects. A task is assigned to an employee via the contactId field and a status via the statusId field.

Dependencies
This table is used to build relationships between tasks in a project. Both the taskId and dependencyId fields map to the tasks table, creating a sequential relationship between the two records.

Timesheets
This table stores the number of hours clocked up against a task by an employee. An optional field for notes is provided so that the user assigned to the task can document any issues arising during the project.

This database lays the groundwork for our Intranet. A script containing the entire SQL schema with some dummy content can be downloaded with this tutorial so that you can have a working copy of it while you build the ASP.NET 2.0 pages in the concluding part of this tutorial.

You can download the source code here.