Understanding Data in the Database

A database is made up of lots of little pieces of data. In the case of Microsoft Project, there are task names, task durations, calendars, resource costs, and so on. Each of these pieces of data that has a column in a database table also has a data type associated with it. Data types are used to define the way the data is stored in the database. For example, a resource cost field needs to hold a number that might not be a whole number, so it needs a data type that allows a number such as 20.50. A task name field needs to hold whatever name the project manager gives it, so this field needs to be able to accept alphanumeric text data.

There are generalized types of data. Basically, four major areas are required: numbers, dates, text, and yes/no data. The OLE DB uses just these basic data types because it's a relatively simple database when compared to the Microsoft Project and Project Server databases, which use more than four data types. For example, the Project 11 OLE DB Provider uses Number for all fields that store non-date numbers; in the Microsoft Project and Project Server databases, numbers are represented by more specific data types such as Int (integer, requiring 4 bytes of storage), Decimal, Float (approximate numbers), Smallint (integer, requiring 2 bytes of storage), or Tinyint (integer, requiring 1 byte of storage), and so on.

A data type restricts the type of data allowed in that field in the database and helps keep the right data in the right place. The following sections describe general data types; specific data types are mentioned also.

Yes/No Data

The Yes/No data type is used when you must choose between two options. Examples include the following:

• Schedule From Start or Schedule From Finish

Fields in the Project OLE DB Provider use the Boolean data type to define this data. In the Microsoft Project and Project Server databases, the fields that contain the same data are either Bit, Smallint, or Tinyint data types. These three data types are used to represent small amounts of data:

• Smallint can store integers ranging from -32,766 to 32,766.

• Tinyint can store integers ranging from 0 to 255.

Dates and Times «

Date and time data types are stored in either Date (Project OLE DB Provider) or Datetime (Microsoft Project and Project Server databases) fields. They both display their data like this: °

mm/dd/yy hh:mm:ss AM/PM. For example: 06/01/04 12:00:00 AM for midnight, June 1, 2004.

Note To see an example of this data type in your project database in Access, look at the TASK EARLY FINISH field in the MSP TASKS table.

Text Data

The text data type data is, quite simply, alphanumeric text; that is, any letters and numbers. It can be the name of a project, a resource, or a task. It can be the text that describes an option in the user interface. In the Project OLE DB Provider, Text is the data type that stores this data. In the Microsoft Project database, the data types Char, Varchar, and Text are used to store this data. In the Project Server database, the data types Char, Nvarchar, and Text are used to store this data.

Typically, the Text data type allows the maximum number of characters (up to 2,147,483,647); whereas Char, Varchar, and Nvarchar have their maximum allowable characters defined in the database using (n) to define the maximum length. For example, the Project Name field cannot store more than 255 characters. In the Microsoft Project database, the data type for this field is Varchar(255).

Specific Values

A specific value data type is information that contains a certain value predetermined by Microsoft Project. For example, a specific value can be an integer that represents a selection you have made in the user interface. It can be a single number or a large, seemingly random number that represents a duration or an amount of work.


How the Project database stores values

If you want to see an example of the way the Microsoft Project database stores specific values based on settings you choose in the user interface, do the following:


In Microsoft Project, open the project you exported as the database.


Click Tools, Options.


On the View tab of the Options dialog box, change Decimal Digits to 1 and Placement to something other than the default.


If necessary, open the project in Access.


Open the MSP_PROJECTS table.



Notice the values in the database and notice the values in the View tab of the Options dialog box. In the case of PROJ_OPT_CURRENCY_DIGITS, the value corresponds to the value shown in the Options dialog box (0, 1, or 2). In the case of PROJ_OPT_CURRENCY_POSITION, the value is 0, 1, 2, or 3; which represents $1 (before), $1 (after), $ 1 (before, with space), or $ 1 (after, with space), with 0 being the first item available in the list.

Miscellaneous Data

There are some data types that store unique types of data. These data types are used sparingly. For example, image data types are used to store binary data in the Microsoft Project and Project Server databases. There is also a Uniqueidentifier data type in the Project Server database that tracks globally unique IDs.

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook

Post a comment