Datasheet

SQL Server schema
The Access schema was small compared to the SQL Server schema shown in Figure 1-8. You will be
doing a limited amount of work on the Project Time Tracker application in Access. Most of your devel-
opment of this application will be done in either SQL Server or Oracle. Therefore, the schema for SQL
Server and Oracle will be larger and comprise all the tables that make up the ProjectTimeTracker
database.
Because you’ve already seen the Groups, Projects, and GroupProjects tables, they won’t be covered
again. Instead, you’ll focus on the other tables shown in the schema. Before you look at the other tables,
however, take note of the data types used in the Groups, Projects, and GroupProjects tables. These data
types have been converted from Access data types to SQL Server data types. A complete data type
cross reference is available in Appendix A.
Of particular interest is the data type for the primary keys. SQL Server has a native data type, called a
UniqueIdentifier, that supports Guids as seen in the schema. SQL Server also provides a built-in
function that can generate Guids to be inserted into a column with this data type. However, you generate
the Guids from your program using the built-in .NET Framework function called
Guid.NewGuid().
Let’s start by examining the Users table. This table contains all users with access to the application and
the basic information about the users. You may have noticed that the Password column is five times the
size of the LoginName column. This is because you will be hashing the user’s password when inserting
a new user in Chapter 11. A hashed password can be almost five times the size of the original password
so this column has been designed to handle a hashed password based on a password that is a maximum
length of 15 characters.
Also note that there is a foreign key relationship to the Groups table. Every user will be assigned to a
group and this key provides the relationship between the Groups and Users tables.
Also notice that there is a foreign key relationship to the Roles table. Every user will be assigned to a
role. The Roles table contains the various roles that may be assigned, such as Administrator, Manager,
and User. The Roles table contains a column called Ranking. The roles will be defined by rank, so a role
of Administrator has a higher ranking than Manager, and the Manager role has a higher ranking than
User. You use this column later when selecting and displaying data from this table.
Finally, notice that there is a foreign key reference from the ManagerID column to the UserID column. A
manager is a user like anyone else, only with a different role. This column allows a
NULL value to be
inserted because you may not want to assign a manager to a user when you enter a user. Once
updated with a value, this column contains the UserID of the manager, enabling you to retrieve the user
information for each user as well as information about his or her manager.
The last two tables in this schema are TimeSheets and TimeSheetItems. The TimeSheets table contains
the basic information about a timesheet for any given week for a user. Notice that this table contains two
foreign key references to the Users table. The first foreign key, UserID, is the foreign key that points to
the user of this timesheet. The second foreign key, ManagerID, points to the manager who has approved
the timesheet; this will be the manager of the user.
The last table in this schema is the TimeSheetItems table. This table contains a row of data for each project
that is assigned to the user in the GroupProjects table. It also contains a row of data for each project for
each day for which the user enters data (see Figure 1-8).
20
Chapter 1
04_58894x ch01.qxd 10/13/05 5:54 PM Page 20