In the first part of this series we are going to look at a range of possible modules, such as contact and document management functions, and construct the SQL Server database for the application.
In part two we'll cover the use of VWD in building a rudimentary contact management module for the Intranet.
Contact management
One of the core requirements of a good intranet is a contact management system. It is also one of the simplest modules to build, being a simple repository of company and personal information.
Because users in a working environment will usually be using an e-mail client with some form of built-in contact management, it can be advantageous to integrate the Intranet's contact management with users' local address books.
This can be done using shared contacts in a Microsoft Exchange environment, in which case the Intranet module can interface directly with Exchange. Otherwise, in the absence of an Exchange server, an export facility is a simple means of allowing users to manually synchronise their contacts with the Intranet.
A basic document repository can be used to track the status of files in an intranet.
Document management
Another central role for an Intranet is to provide a centralised document library which offers relationships and management capabilities beyond that of simple file storage on a server.
For instance, document versioning, work- flow and the ability to check files in and out are all basic functions that a document library has to offer.
The choice as to whether to store documents on disk or within a database will depend upon other factors such as the security and availability requirements of the application.
Project management and timesheets
Project management capabilities can be attached to a contact management system, making it possible to assign projects to clients, and then assign resources to projects.
If a document library is also in place, files can be assigned to clients, resources and projects as well. For extra functionality, projects can also be broken down into tasks, each with dependencies, a status and a user responsible for completing it. Users in turn can complete time sheets at the end of each day, showing how many hours have been spent on each task.
Shared calendars and resource booking
Although the three modules described above form the foundation for most Intranets, calendars and booking schedules can be handy for managing resources such as meeting rooms and office equipment.
As with contact management, bookings and shared calendars stored within an Intranet can duplicate functions already being performed by an e-mail client like Microsoft Outlook.
In this situation you will need to evaluate the best way of integrating the two systems. For instance, it may be possible to integrate with Exchange server shared calendars or to write VBScript macros that connect to Intranet Web services to synchronise meeting and calendar bookings.
Alternatively, it is also possible to use an Intranet calendar to generate meeting invitations and the like via e-mail. These can be enhanced to integrate with Outlook or any other applications being used for their calendar functions.
Building the database
Before coding any pages, we need to design and build the database. In the diagram below we have established 12 tables as storage for the Intranet modules discussed above. Let's take a quick look at each of these.
The database structure for the intranet.
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
|
