 |
|
EnterpriseDB: Data Model
Oracle Tips by Burleson Consulting
|
The
data model for TimeTraker is a relational model taking advantage of
database provided referential integrity. Figure 9.2 provides the
TimeTraker Entity Relationship Diagram.
Figure
9.2: TimeTraker Entity Relationship Diagram
Data
Dictionary
The
data in Table 9.1 describes each of the tables in the data model in
figure 9.2 and table 9.2 provides definitions for each column in
each table.
|
TABLE |
DESCRIPTION |
|
TT_SCHEDULES |
Work schedules such as "Monday through Friday" |
|
TT_XML_INTERFACE |
Interface for external programs such as card swipers to drop XML
documents containing user time information |
|
TT_TASKS |
Defined work tasks |
|
TT_PROJECTS |
Defined work projects |
|
TT_RESOURCES |
Defined resources that will be assigned to tasks |
|
TT_PROJECTS_TASKS |
A
definition of tasks in projects |
|
TT_TASKS_RESOURCES |
A
definition of resources assigned to tasks |
|
TT_TASK_ITEM_UPDATE |
A
record of all time worked on all tasks |
|
TT_TASK_DEPENDS |
Dependencies between tasks |
|
TT_CALENDAR |
Business calendar for information. Includes a business day
definition as well as holidays. |
Table
9.1: All TimeTraker Tables
|
COLUMN |
DESCRIPTION |
|
TT_CALENDAR
DATE_PK |
Calendar Primary Key, Date Only, No Timestamp |
|
QUARTER_IND |
Quarter as defined by business (1,2,3,4) |
|
HOLIDAY_FLAG |
Is
this date a holiday |
|
BUSINESS_DAY_FLAG |
Is
this date a business day |
|
TT_PROJECTS
PROJECT_ID |
Project Primary Key, tt_projects_seq |
|
PROJECT_NAME |
Name of the project |
|
PROJECT_DESC |
Description of the project |
|
ACTIVE_FLAG |
Is
this
project still active |
|
PRIORITY |
Priority of the task in this project |
|
ESTIMATED_HOURS |
Estimated Hours to complete this task |
|
PROJECT_TASK_NAME |
Name of this task in this project at this step |
|
FILE_ATTACHMENTS_FLAG
|
Are there any attachments |
|
ACTIVE_FLAG |
Is
this an active record? |
|
TT_RESOURCES
RESOURCE_ID
PK |
|
|
SCHEDULE_ID |
FK
to resource schedule |
|
RESOURCE_NAME |
Resources Name |
|
ALLOCATION_PCT
|
What percentage of the day can this resource be allocated to
tasks, informational only |
|
HOURS_PER_DAY |
What
hours per day is this resource available, informational only |
|
JOB
|
What is this resources primary job, informational only |
|
ASSIGNABLE_FLAG |
Can this resource be assigned to tasks? |
|
XML_FEED_USER_ID |
What is this resources ID when receiving records from external
programs? |
|
ACTIVE_FLAG |
Is
this an active record? |
|
TT_SCHEDULES
SCHEDULE_ID
PK |
|
|
SCHEDULE_TYPE |
Name of the schedule |
|
MONDAY |
Y/N Flag |
|
TUESDAY |
Y/N Flag |
|
WEDNESDAY |
Y/N Flag |
|
THURSDAY |
Y/N Flag |
|
FRIDAY |
Y/N Flag |
|
SATURDAY |
Y/N Flag |
|
SUNDAY |
Y/N Flag |
|
TT_TASKS
TASK_ID
PK |
|
|
TASK_NAME |
Name of the task |
|
TASK_DESC |
Description of the task |
|
ACTIVE_FLAG |
Is
this an active record? |
|
TT_TASKS_RESOURCES
TASK_ID
PK
RESOURCE_ID
PK |
|
|
ACTIVE_FLAG |
Is
this an active record? |
|
PROJECT_ID
PK
TASK_ID
PK
TT_TASK_DEPENDS |
|
|
DEPEND_PROJECT_ID |
Dependant project |
|
DEPEND_TASK_ID |
Dependant task |
|
TT_TASK_ITEM_UPDATE
TASK_ID
PK
RESOURCE_ID
PK |
|
|
ITEM_DATE |
Date of work |
|
HOURS_WORKED |
Number of hours worked |
|
ACTIVE_FLAG |
Is
this an
active
record? |
|
TT_XML_INTERFACE
INTERFACE_ID
PK |
|
|
DATE_RECEIVED |
Date record was uploaded |
|
XML_DATA |
XML Document describing a task item update |
Table
9.2: All TimeTraker Column
The Application
The
application is based around a set of packages. Each package
operates on a specific area of the application. There are six
packages and no stand-alone procedures or functions. The table in
9.3 describes each package.
|
PACKAGE |
DESCRIPTION |
|
TT_CALENDAR_ADMIN |
PM
and Admin, manage the calendar |
|
TT_MANAGE_PROJECTS |
PM, add and deactivate projects and tasks |
|
TT_MANAGE_RESOURCES |
PM, add and deactivate resources |
|
TT_MANAGE_USER_RECORDS |
User, add time worked |
|
TT_REPORT_OUTPUT |
Everyone, run reports |
|
TT_USER_ADMIN |
Admin only, add system users |
Table
9.3: TimeTraker Packages
Table
9.4 lists all of the procedures and functions in the application.
|
PROCEDURES AND FUNCTIONS |
DESCRIPTION |
|
TT_CALENDAR_ADMIN
CHANGE_BUSINESS_DAY_FLAG |
Change a calendar entry and set or unset
the business day flag |
|
TT_CALENDAR_ADMIN
CHANGE_HOLIDAY_ FLAG |
Change a calendar entry by setting or unsetting the holiday flag |
|
TT_CALENDAR_ADMIN
GET_SCHEDULE_ID |
Get a schedule ID, used internally |
|
TT_CALENDAR_ADMIN
GET_SCHEDULE_TYPE |
Get a schedule type |
|
TT_CALENDAR_ADMIN
POPULATE_ CALENDAR |
Auto-populate the calendar for a range of dates |
|
TT_CALENDAR_ADMIN
SCHEDULE |
Add or change a schedule |
|
TT_MANAGE_PROJECTS
ASSIGN_RESOURCE_TO_ TASK |
Associate a resource to a task |
|
TT_MANAGE_PROJECTS
ASSIGN_TASK_TO_ PROJECT |
Associate a task to a project |
|
TT_MANAGE_PROJECTS
CREATE_ITEM_ DEPENDANCIES |
Relate projects and tasks to other projects and tasks |
|
TT_MANAGE_PROJECTS
CREATE_PROJECT |
Create a new project |
|
TT_MANAGE_PROJECTS
CREATE_TASKS |
Create a new task |
|
TT_MANAGE_PROJECTS
DEACTIVATE_TASK_ FROM_PROJECT |
Set a task to inactive |
|
TT_MANAGE_PROJECTS
GET_PROJECT_ID |
Retrieve a project primary key, used internally |
|
TT_MANAGE_PROJECTS
GET_TASK_ID |
Retrieve a task primary key, used internally |
|
TT_MANAGE_PROJECTS
REACTIVATE_TASK_TO_ PROJECT |
Reactivate an inactive task |
|
TT_MANAGE_ RESOURCES
ADD_RESOURCE |
Create a new resource, this is not a system user just a resource
to be assigned to tasks |
|
TT_MANAGE_ RESOURCES
ALTER_RESOURCE |
Modify a resource |
|
TT_MANAGE_ RESOURCES
DELETE_RESOURCE |
Inactivate a resource |
|
TT_MANAGE_ RESOURCES
GET_RESOURCE_ID |
Retrieve a resource ID, used internally |
|
TT_MANAGE_USER_ RECORDS
ADD_NEW_ITEM_UPDATE |
Update hours worked |
|
TT_MANAGE_USER_ RECORDS
ADD_XML_RECORD |
Add an interface XML document |
|
TT_MANAGE_USER_ RECORDS
PROCESS_XML_DATA |
Process the XML Interface |
|
TT_REPORT_OUTPUT
HOURS_WORKED_BY_ RESOURCES |
A
report listing all of the hours worked by all resources |
|
TT_REPORT_OUTPUT
PROJECT_RESOURCES |
A
list of all resources assigned to projects |
|
TT_REPORT_OUTPUT
PROJECT_TASKS |
A
list of all tasks and all projects |
|
TT_REPORT_OUTPUT
PROJ_HOURS_REMAINING |
A
listing of the number of hours remaining in all projects |
|
TT_REPORT_OUTPUT
PROJ_HOURS_REMAIN_ BY_RESOURCE |
A
listing of the number of hours remaining in all projects by the
hours worked by resources assigned to that project |
|
TT_USER_ADMIN
CHANGE_USER |
Modify an existing database user |
|
TT_USER_ADMIN
CREATE_USER |
Add a new database user |
|
TT_USER_ADMIN
DROP_USER |
Drop a user |
Table
9.4: TimeTraker Procedures and Functions
All
source code for the Oracle version of TimeTraker can be downloaded
from the code depot. The source code includes creating the Traker
user, granting the appropriate permission and creating the entire
schema. Code files for compiling in Oracle begin with the word
oracle and should be run in the order below:
*
chap9_oracle_create_traker_schema
*
chap9_oracle_create_traker_db_objects.sql
*
chap9_oracle_traker_views.sql
*
chap9_oracle_traker_packages.sql
*
chap9_oracle_traker_grants_synonyms.sql
*
chap9_oracle_traker_sample_data.sql
*
chap9_oracle_create_traker_schema.sql
prompt conn sys@traker as sysRemote DBA
create tablespace traker_tbs datafile 'c:\oracle\oradata\traker_tbs.dbf'
size 25M
reuse autoextend on maxsize unlimited;
create user traker identified by traker
default tablespace traker_tbs
quota unlimited on traker_tbs;
grant Remote DBA to traker;
grant connect, resource to traker with admin option;
grant select on Remote DBA_role_privs to traker;
grant create user, drop user, alter user to traker;
~production directive: code_file:
chap9_oracle_create_traker_db_objects.sql
CREATE ROLE tt_user_role;
CREATE ROLE tt_pm_role;
CREATE ROLE tt_admin_role;
CREATE SEQUENCE tt_general_sequence;
CREATE DIRECTORY report_output AS 'c:\temp';
Prompt
Table TT_SCHEDULES;
CREATE TABLE TRAKER.TT_SCHEDULES
(
SCHEDULE_ID NUMBER(12) NOT NULL,
SCHEDULE_TYPE VARCHAR2(10),
MONDAY VARCHAR2(1),
TUESDAY VARCHAR2(1),
WEDNESDAY VARCHAR2(1),
THURSDAY VARCHAR2(1),
FRIDAY VARCHAR2(1),
SATURDAY VARCHAR2(1),
SUNDAY VARCHAR2(1),
PRIMARY KEY
(SCHEDULE_ID),
CONSTRAINT TT_SCHEDULES_UK1
UNIQUE (SCHEDULE_TYPE)
);
Prompt
Table TT_XML_INTERFACE;
CREATE TABLE TRAKER.TT_XML_INTERFACE
(
INTERFACE_ID NUMBER(12) NOT NULL,
DATE_RECEIVED TIMESTAMP(6),
XML_DATA SYS.XMLTYPE,
PRIMARY KEY
(INTERFACE_ID)
);
Prompt
Table TT_TASKS;
CREATE TABLE TRAKER.TT_TASKS
(
TASK_ID NUMBER(12) NOT NULL,
TASK_NAME VARCHAR2(100),
TASK_DESC VARCHAR2(4000),
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(TASK_ID),
CONSTRAINT TT_TASKS_UK1
UNIQUE (TASK_NAME)
);
Prompt
Table TT_PROJECTS;
CREATE TABLE TRAKER.TT_PROJECTS
(
PROJECT_ID NUMBER(12) NOT NULL,
PROJECT_NAME VARCHAR2(100),
PROJECT_DESC VARCHAR2(4000),
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(PROJECT_ID),
CONSTRAINT TT_PROJECTS_UK1
UNIQUE (PROJECT_NAME)
);
Prompt
Table TT_CALENDAR;
CREATE TABLE TRAKER.TT_CALENDAR
(
DATE_PK DATE NOT NULL,
QUARTER_IND NUMBER(2),
HOLIDAY_FLAG VARCHAR2(1),
BUSINESS_DAY_FLAG VARCHAR2(1),
PRIMARY KEY
(DATE_PK)
);
Prompt
Table TT_RESOURCES;
CREATE TABLE TRAKER.TT_RESOURCES
(
RESOURCE_ID NUMBER(12) NOT NULL,
SCHEDULE_ID NUMBER(12) NOT NULL,
RESOURCE_NAME VARCHAR2(100),
ALLOCATION_PCT NUMBER(3),
HOURS_PER_DAY NUMBER(6,4),
JOB VARCHAR2(100),
ASSIGNABLE_FLAG VARCHAR2(1),
XML_FEED_USER_ID VARCHAR2(100),
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(RESOURCE_ID),
CONSTRAINT TT_RESOURCES_UK1
UNIQUE (RESOURCE_NAME),
CONSTRAINT TT_RESOURCES_UK2
UNIQUE (XML_FEED_USER_ID),
FOREIGN KEY (SCHEDULE_ID)
REFERENCES TRAKER.TT_SCHEDULES (SCHEDULE_ID)
);
Prompt
Table TT_PROJECTS_TASKS;
CREATE TABLE TRAKER.TT_PROJECTS_TASKS
(
PROJECT_ID NUMBER(12) NOT NULL,
TASK_ID NUMBER(12) NOT NULL,
PRIORITY NUMBER(5),
ESTIMATED_HOURS NUMBER(12),
PROJECT_TASK_NAME VARCHAR2(100),
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(PROJECT_ID, TASK_ID),
FOREIGN KEY (TASK_ID)
REFERENCES TRAKER.TT_TASKS (TASK_ID),
FOREIGN KEY (PROJECT_ID)
REFERENCES TRAKER.TT_PROJECTS (PROJECT_ID)
);
Prompt
Table TT_TASKS_RESOURCES;
CREATE TABLE TRAKER.TT_TASKS_RESOURCES
(
TASK_ID NUMBER(12) NOT NULL,
RESOURCE_ID NUMBER(12) NOT NULL,
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(TASK_ID, RESOURCE_ID),
FOREIGN KEY (RESOURCE_ID)
REFERENCES TRAKER.TT_RESOURCES (RESOURCE_ID),
FOREIGN KEY (TASK_ID)
REFERENCES TRAKER.TT_TASKS (TASK_ID)
);
Prompt
Table TT_TASK_ITEM_UPDATE;
CREATE TABLE TRAKER.TT_TASK_ITEM_UPDATE
(
TASK_ID NUMBER(12) NOT NULL,
RESOURCE_ID NUMBER(12) NOT NULL,
ITEM_DATE DATE NOT NULL,
HOURS_WORKED NUMBER(6,4),
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(TASK_ID, RESOURCE_ID, ITEM_DATE),
FOREIGN KEY (TASK_ID, RESOURCE_ID)
REFERENCES
TRAKER.TT_TASKS_RESOURCES (TASK_ID,RESOURCE_ID)
);
Prompt
Table TT_TASK_DEPENDS;
CREATE TABLE TRAKER.TT_TASK_DEPENDS
(
PROJECT_ID NUMBER(12) NOT NULL,
TASK_ID NUMBER(12) NOT NULL,
DEPEND_PROJECT_ID NUMBER(12) NOT NULL,
DEPEND_TASK_ID NUMBER(12) NOT NULL,
PRIMARY KEY
(PROJECT_ID, TASK_ID),
FOREIGN KEY (DEPEND_PROJECT_ID, DEPEND_TASK_ID)
REFERENCES TRAKER.TT_PROJECTS_TASKS (PROJECT_ID,TASK_ID),
FOREIGN KEY (PROJECT_ID, TASK_ID)
REFERENCES TRAKER.TT_PROJECTS_TASKS (PROJECT_ID,TASK_ID)
);
Prompt
Index TT_CALENDAR_QTR_NDEX;
CREATE INDEX TRAKER.TT_CALENDAR_QTR_NDEX ON
TRAKER.TT_CALENDAR
(QUARTER_IND);
Prompt
Index TT_CALENDAR_HOL_NDX;
CREATE INDEX TRAKER.TT_CALENDAR_HOL_NDX ON
TRAKER.TT_CALENDAR
(HOLIDAY_FLAG);
Prompt Index TT_CALENDAR_BUS_NDX;
CREATE INDEX
TRAKER.TT_CALENDAR_BUS_NDX ON TRAKER.TT_CALENDAR
(BUSINESS_DAY_FLAG);
Prompt
Index TT_PROJECTS_TASKS_NAME_NDX;
CREATE UNIQUE INDEX TRAKER.TT_PROJECTS_TASKS_NAME_NDX
ON TRAKER.TT_PROJECTS_TASKS
(PROJECT_TASK_NAME, ACTIVE_FLAG);
Prompt
Index TT_TASK_DEPENDS_ALL_NDX;
CREATE INDEX
TRAKER.TT_TASK_DEPENDS_ALL_NDX ON TRAKER.TT_TASK_DEPENDS
(DEPEND_PROJECT_ID, DEPEND_TASK_ID, PROJECT_ID, TASK_ID);
Prompt
Index TT_TASK_ITEM_UPDATE_RES_NDX;
CREATE INDEX
TRAKER.TT_TASK_ITEM_UPDATE_RES_NDX ON TRAKER.TT_TASK_ITEM_UPDATE
(RESOURCE_ID);
Prompt
Index TT_XML_INTERFACE_DT_NDX;
CREATE INDEX TRAKER.TT_XML_INTERFACE_DT_NDX ON
TRAKER.TT_XML_INTERFACE
(DATE_RECEIVED, INTERFACE_ID);
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
