Sample SQL Server Database Description
From a document presented to a client
Altered to protect confidential information
This document describes the proposed SQL Server database for immediate implementation. It is intended that this document will change as the database’s tables change. The description includes a chart depicting the basic tables and their relationships followed by a brief description of each table in its most basic form.
For the most part, each table description includes only the fields necessary to represent the purpose of the table and its relationships to other tables. After the description of the basic tables comes a section that more fully describes the tables with all the fields necessary to implement the Web-based product that also includes the new features being implemented parallel to the development of this database.
Special attention has been paid to flexibility in the design of the tables. The tables are capable of supporting growth in the product’s capabilities. Some of the more obvious enhancement possibilities are pointed out in this document. That they are mentioned in no way implies that these features must or should be implemented.
In general, the tables in the chart show one-to-many relationships flowing from left to right. One-to-one relationships are portrayed using connectors without arrows. A few tables implement a many-to-many relationship between two other tables.
One special table, the “Types” table, receives special attention in its own section. Briefly, this table contains meta-data that enables the creation of data types that are completely analogous to existing data types. Some fields change meaning (even change record relationships) depending on “type” information. Fields affected in this way are colored gray. Dotted lines depict affected relationships.
Fields colored cyan represent hierarchical relationships between records within the same table.
Meta-data used in many tables to differentiate among analogous data types. For example, it is used to distinguish people from managers in the People table. For a more complete description including current table entries, see the “Types” section below.
Index (Number): Used by other tables to reference this record. Not auto-numbered because of the semantic importance of this field.
Type (Text*50): Brief type description.
Table (Text*50): Name of the table where this type is used.
Table of Projects that can be used as tasks. To assign a project to a person, it must first be assigned to an org with a due date via the OrgTasks table, then an entry can be made in the Tasks table. These records keep track of Projects as a group because more than one version of the same project can exist. When binding the project to a person record to form an task, the current version is used. Each project version is stored in the ProjectVersions table.
Index (AutoNumber): Used by other tables to uniquely reference this record.
CurrentVersion (Number): Index of the latest version available.
Table containing each version of a particular project.
Index (AutoNumber): Used by other tables to uniquely reference this record.
VersionOf (Number): Index of the project represented by this version.
Description (Text*50): Short description of the project.
WeightedItems (Number): Number of weighted items; used for scoring.
Table of organizations of or within departments. The most important organization for task purposes is the team (or variants thereof). But this table can be used to represent a hierarchy or matrix of organizations within a department or company.
Though the current product may not support this, the table is designed for extended functionality that could be added in the future. For example, a company may not call its basic organizational unit a “team”. It is sufficient to change the corresponding entry in the Types table to effect the nomenclature change. Also, it can become possible to make tasks across units by associating them with super units such as “Visual Basic Groups” or “All Teams Using Network 7”.
Index (AutoNumber): Used by other tables to uniquely reference this record.
Name (Text*50): Organization name.
TypeIndex (Number): Corresponds to Types.Index. Indicates the type of organization. Should only include values marked “Orgs” in the type table.
Parent (Number): Corresponds to Orgs.Index. This optional field arranges the organizations in a hierarchy.
Represents the hierarchical organization of Projects. The basic unit is the checkpoint. In conjunction with the Types table, it is possible to conceive of any number of schemes to hierarchically organize a project. For the present, we will represent the basic organization supported by the current product.
Index (AutoNumber): Used by other tables to uniquely reference this record.
ProjectIndex (Number): Corresponds to ProjectVersions.Index. Associates the section to its project.
Number (Number): Absolute number of this section within its project. One-based.
TypeIndex (Number): Corresponds to Types.Index. Indicates the type of section. Should only include values marked “Sections” in the type table except for the special type “Item”.
Parent (Number): Corresponds to Sections.Index. This optional field arranges the sections in a hierarchy.
Comprehensive list of all persons who have access to the data. Any types of person with various levels of data access can be included. The kind of person—and the corresponding access privileges—are determined by the associated entry in the Types table. Note that there is no support here for a hierarchy, instead note that the OrgsPeople table is indirectly hierarchical via the Orgs table.
Index (AutoNumber): Used by other tables to uniquely reference this record.
Login (Text*50): The login name assigned to the user.
Password (Text*50): Password used to verify login privileges.
Name (Text*50): The person’s name.
TypeIndex (Number): Corresponds to Types.Index. Indicates the type of person, specifically the type of access this person is allowed. Should only include values marked “People” in the type table.
Implements a many-to-many relationship between Orgs and People. Other tables do not reference this auxiliary table. Instead, this table is used to find all people within a given organization.
Index (AutoNumber): Used by other tables to uniquely reference this record.
PeopleIndex (Number): Corresponds to People.Index. Ties the person referenced to the organization referenced.
OrgIndex (Number): Corresponds to Orgs.Index. Ties the organization referenced to the person referenced.
Intended to assign a project to a team though, in theory, any organization can receive an task in this fashion. Implements a many-to-many relationship between Projects and Orgs. This intermediate table does not represent actual tasks, instead the Tasks table completes the work of turning a project into an task by associating a OrgTask to a person. The due date can be assigned here but can be overridden in individual person cases.
Index (AutoNumber): Used by other tables to uniquely reference this record.
ProjectIndex (Number): Corresponds to Projects.Index. Assigns this project to the organization referenced.
DueDate (Date/Time): Attaches a due date to the task that can be overridden by individual due dates in the Tasks records.
OrgIndex (Number): Corresponds to Orgs.Index. Assigns the referenced project to this organization.
Represents individual items, such as questions, within a project. Items are linked indirectly to Projects via their respective pages represented in Sections table.
Index (AutoNumber): Used by other tables to uniquely reference this record.
TypeIndex (Number): Corresponds to Types.Index. Indicates the type of item mostly for scoring purposes. Should only include values marked “Items” in the type table.
SectionIndex (Number): Corresponds to Sections.Index. Associates this item to its page in the Sections table.
Number (Number): One-based number of the item on its page.
Allows the association of comments on a given task to individual items or to pages. In theory, any project section could receive a comment if so implemented.
Index (AutoNumber): Used by other tables to uniquely reference this record.
TaskIndex (Number): Corresponds to Tasks.Index. Associates this comment to an task (and thus to a particular person’s work) in the Tasks table.
TypeIndex (Number): Corresponds to Types.Index. Indicates the type of item being commented on. Should only include values marked “Sections” in the type table including the special type “Item”.
ItemIndex (Number): Corresponds to Sections.Index or to Items.Index depending on the type. If the type is “Item”, associates this record to a record in the Items table; otherwise, the comment is associated with a section (a page) in the Sections table.
Comment (Memo): The comment itself.
Associates
entries in the OrgTasks table with people as entries in the People table. Each record has a due date that can override
the due date found in the OrgTasks record.
Creates a many-to-many relationship between OrgTasks and People.
Index (AutoNumber): Used by other tables to uniquely reference this record.
OrgTaskIndex (Number): Corresponds to OrgTasks.Index. Associates this record with an org task.
ProjectVersion: (Number): Associates the person to a particular version of the project.
PeopleIndex (Number): Corresponds to People.Index. Associates a person (person) with this record.
DueDate (Date/Time): Copied from OrgTasks.DueDate. If changed from this default, it should be considered to override the default.
For
project items such as multiple choice or fill in the blank, this tables serves
as the possible goals to the question.
Index (AutoNumber): Used by other tables to uniquely reference this record.
ItemIndex (Number): Corresponds to Items.Index. Associates the choice to its item (question) within the project.
Number (Number): Absolute number of this choice within its item. One-based.
Tables
containing the goals (or goal suggestions) for given project items. The three tables cover three different goal
formats: numerical (mostly for indicating the correct choice among several),
long text and short text. How each
record is used depends entirely on the type of question (project item) is being
scored.
Index (AutoNumber): Used by other tables to uniquely reference this record.
ItemIndex (Number): Corresponds to Items.Index. Associates the goal to its item (question) within the project.
Goal (Number or Memo or Text*50): Numerical goals usually will refer to which choice (by index) in the Choices table that is the correct goal. In the text tables, the text goal is either the (near-) exact or suggested goal (for manual scoring).
Table
of records, one for each item in each task that has person outcomes. Each record has a one-to-one relationship
with the actual outcome that is contained in one of three tables depending on
the outcome type. The main outcome
records (contained in this table) have a field that indicates whether the outcome
was correct.
Index (AutoNumber): Used by other tables to uniquely reference this record.
TaskIndex (Number): Corresponds to Tasks.Index. Associates this record with an task.
ItemIndex (Number): Corresponds to Items.Index. Associates this record with an item in the project corresponding to the task.
ItemScore (Number): If scored, indicates the items weighted score.
The actual outcomes in correspondence with the main outcome records found in the Outcomes table. Outcomes are stored in one of these three tables depending on the project item type. For the most part, numerical outcomes indicate which choice out of a number of (choice) choices has been made.
Index (AutoNumber): Used by other tables to uniquely reference this record.
OutcomeIndex (Number): Corresponds to Outcomes.Index. Forms a one-to-one correspondence between this record and its base record in the Outcomes table.
Outcome (Number or Memo or Text*50): Numerical outcomes usually will refer to which choice (by index) in the Choices table was chosen. Text outcomes are stored in the text tables depending on whether the outcome is expected to be long or short.
The Types table contains meta-data used to determine semantic information in other tables. By adding a new type for an already defined table, data analogous to the data already in the table can be entered in the database without defining a new table.
For example, in the People table, entries can be made for both programmers and managers. The TypeIndex field that refers to the Types table records for “Programmer” and “Manager” differentiates them. It is possible to give different names to these entities by replacing the Types table entry. So, if it is preferable to call managers “team leaders”, the Types table entry can be changed to use the term “Team Leader”. There is no need to define separate tables for programmers and managers because the data for both kinds of people is analogous and uses exactly the same table structure.
The key is that the software knows about pre-determined Index numbers in the Types table. Again, using the People table for an example, the software will treat managers differently than programmers because their People records have different TypeIndex values. Programmers would be granted different logon privileges than managers, for instance.
This means that the Types table must contain pre-defined entries whenever a fresh database is created. The table shown on the next page is an example of what would be the typical contents of the Types table. Starred (*) items are those that must be present to support the current web-based product.
The Types table also allows the software to create and manage new types on the fly. The table includes a field that indicates in which table the entry is used. Thus, it could be possible to divide Projects in various ways rather than in pre-defined ways. One project could consist only of pages, another of sections with pages or chapters with sub-chapters each with pages. The software would enforce the page as the basic unit and allow project sections to be built using new entries in the Types table with unique Index values.
|
* |
Index |
Type |
Table |
|
* |
1 |
Manager |
People |
|
* |
2 |
Programmer |
People |
|
|
3 |
Administrator |
People |
|
|
4 |
Publisher |
People |
|
* |
5 |
Team |
Orgs |
|
|
6 |
Section |
Orgs |
|
|
7 |
Division |
Orgs |
|
|
8 |
Company |
Orgs |
|
|
9 |
Area |
Orgs |
|
|
10 |
Department |
Orgs |
|
* |
11 |
Multiple Choice |
Items |
|
|
12 |
Fill in Circle |
Items |
|
|
13 |
Draw a Mark |
Items |
|
|
14 |
Connect a Line |
Items |
|
|
15 |
Drag and Drop |
Items |
|
^ |
16 |
Fill in the Blank |
Items |
|
* |
17 |
Essay (Short Goal) |
Items |
|
|
18 |
Budget Authorization |
Sections |
|
|
19 |
Project |
Sections |
|
|
20 |
Subproject |
Sections |
|
^ |
21 |
Checkpoint |
Sections |
|
^ |
22 |
Item |
Sections |
|
Types |
|
Meta-data used in many tables to differentiate among analogous data types. |
|
Index |
Number |
Used by other tables to reference this record. Not auto-numbered because of the semantic importance of this field. |
|
Type |
Text*50 |
Brief type description. |
|
Table |
Text*50 |
Name of the table where this type is used. |
|
Projects |
|
Table of Projects that can be used as tasks. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
CurrentVersion |
Number |
Identifies the latest version. Used to bind current version to task. |
|
ActivitieVersions |
|
Table of Projects that can be used as tasks. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
VersionOf |
Number |
Index of the project represented by this version. |
|
Description |
Text*50 |
Short description of the project. |
|
WeightedItems |
Number |
Number of weighted items; used for scoring. |
|
Orgs |
|
Table of organizations of or within departments. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
Name |
Text*50 |
Organization name. |
|
TypeIndex |
Number |
Corresponds to Types.Index. Indicates the type of organization. Should only include values marked “Orgs” in the type table. |
|
Parent |
Number |
Corresponds to Sections.Index. This optional field arranges the sections in a hierarchy. |
|
Sections |
|
Represents the hierarchical organization of Projects. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
ProjectIndex |
Number |
Corresponds to ProjectVersions.Index. Associates the section to its project. |
|
Number |
Number |
Absolute number of this section within its project. One-based. |
|
TypeIndex |
Number |
Corresponds to Types.Index. Indicates the type of section. Should only include values marked “Sections” in the type table except for the special type “Item”. |
|
Parent |
Number |
Corresponds to Sections.Index. This optional field arranges the sections in a hierarchy. |
|
FilePath |
Text*35 |
For pages; the path (within a known directory) to the page graphic. |
|
WavePath |
Text*35 |
For pages; the path (within a known directory) to the wave file. |
|
People |
|
Comprehensive list of all persons who have access to the data. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
Login |
Text*50 |
The login name assigned to the user. |
|
Password |
Text*50 |
Password used to verify login privileges. |
|
Name |
Text*50 |
The person’s name. |
|
TypeIndex |
Number |
Corresponds to Types.Index. Indicates the type of person, specifically the type of access this person is allowed. Should only include values marked “People” in the type table. |
|
OrgsPeople |
|
Implements a many-to-many relationship between Orgs and People |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
PeopleIndex |
Number |
Corresponds to People.Index. Ties the person referenced to the organization referenced. |
|
OrgIndex |
Number |
Corresponds to Orgs.Index. Ties the organization referenced to the person referenced. |
|
OrgTasks |
|
Assigns a project to an orgroom (or other organization). |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
ProjectIndex |
Number |
Corresponds to Projects.Index. Assigns this project to the organization referenced. |
|
DueDate |
Date/Time |
Attaches a due date to the task that can be overridden by individual due dates in the Tasks records. |
|
OrgIndex |
Number |
Corresponds to Orgs.Index. Assigns the referenced project to this organization. |
|
TimeLimit |
Number |
Time limit for the person to do the task in minutes. |
|
StartDate |
Date/Time |
Attaches an effective date for the task that can be overridden by individual start dates in the Tasks records. |
|
Items |
|
Represents individual items, such as questions, within a project |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
TypeIndex |
Number |
Corresponds to Types.Index. Indicates the type of item mostly for scoring purposes. Should only include values marked “Items” in the type table. |
|
SectiionIndex |
Number |
Corresponds to Sections.Index. Associates this item to its page in the Sections table. |
|
Number |
Number |
One-based number of the item on its page. |
|
XSound |
Number |
X position of any sound icon. |
|
YSound |
Number |
Y position of any sound icon. |
|
WavePath |
Text*35 |
Path (in known directory) to any associated sound file. |
|
ScoringWeight |
Number |
Used to weight the item for scoring. |
|
Sample |
Yes/No |
Marks sample item not included in scoring. NOTE: These Yes/No fields are mutually exclusive. |
|
Ignore |
Yes/No |
Marks item to be ignored in scoring. |
|
Credit |
Yes/No |
Marks item given automatic credit in scoring. |
|
Bonus |
Yes/No |
Marks a bonus item for scoring. |
|
Comments |
|
Allows the association of comments on a given task to individual items or to pages (or other project sections). |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
TaskIndex |
Number |
Corresponds to Tasks.Index. Associates this comment to an task (and thus to a particular person’s work) in the Tasks table. |
|
TypeIndex |
Number |
Corresponds to Types.Index. Indicates the type of item being commented on. Should only include values marked “Sections” in the type table including the special type “Item”. |
|
ItemIndex |
Number |
Corresponds to Sections.Index or to Items.Index depending on the type. If the type is “Item”, associates this record to a record in the Items table; otherwise, the comment is associated with a section (a page) in the Sections table. |
|
Comment |
Memo |
The comment itself. |
|
Tasks |
|
Associates entries in the OrgTasks table with people as entries in the People table. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
OrgTaskIndex |
Number |
Corresponds to OrgTasks.Index. Associates this record with an org task. |
|
ProjectVersion |
Number |
Associates the person with a particular version of the project. |
|
PeopleIndex |
Number |
Corresponds to People.Index. Associates a person (person) with this record. |
|
DueDate |
Date/Time |
Copied from OrgTasks.DueDate. If changed from this default, it should be considered to override the default. |
|
StartDate |
Date/Time |
Copied from OrgTasks.StartDate. If changed from this default, it should be considered to override the default. |
|
ScoredItemCount |
Number |
The number of items included in the scoring. |
|
NumberRight |
Number |
The number of items counted as correct. |
|
TaskScore |
Number |
The computed score in percent. |
|
TaskTimeElapsed |
Number |
If task is incomplete, indicates the number of seconds (?) that the person has spent so far. |
|
TaskDate |
Date/Time |
The date the task was completed. |
|
TaskStatus |
Text*1 |
“C” for complete, “I” for incomplete. |
|
Choices |
|
For project items such as multiple choice or fill in the blank, this tables serves as the possible goals to the question. |
|
Index |
AutoNumber |
Used by other tables to uniquely reference this record. |
|
ItemIndex |
Number |
Corresponds to Items.Index. Associates the choice to its item (question) within the project |
|
Number |
Number |
Absolute number of this choice within its item. One-based. |
|
Left |
Number |
Location of the choice on the page. |
|
Top |
Number |
Location of the choice on the page. |
|
Right |
Number |
Location of the choice on the page. |
|
Bottom |
Number |
Location of the choice on the page. |