A "current information note" is used as a quick reference for employees' temporary contact information and other special items. Users are reminded to maintain their current information note in their workspace.
Email notifications remind users to keep their contact and current information note up to date.
Typical projects are
project_tracker_projects table stores the main project information.
create sequence project_tracker_projects_id_seq;
create table project_tracker_projects (
project_id integer not null primary key,
creator_user_id integer references users,
creation_date date,
name varchar(200),
--- the url of the staff server for this service, if applicable
glassroom_url varchar(100),
start_date date,
end_date date,
-- if this project is for a customer, which one
customer_id integer references project_tracker_customers,
--- purpose is sales, operations, customer
purpose varchar(100),
--- major considerations for this project
notes varchar(4000),
);
Employees of the company are assigned to projects.
create table project_tracker_assignments (
user_id integer references users,
project_id integer references project_tracker_projects,
creator_user_id integer references users,
creation_date date,
-- what the user does for this project
role varchar(4000),
unique(project_id, user_id)
);
Each project has major deadlines. Example
deadlines are "initial launch", "site upgrade",
or "progress checkpoint".
create sequence project_tracker_deadline_id_seq;
create table project_tracker_deadlines (
deadline_id integer primary key,
creator_user_id integer references users,
creation_date date,
name varchar(200),
start_date date,
due_date date,
description varchar(4000),
status_note varchar(4000)
);
User's record time spent on each project or deadline.
create table project_tracker_hours (
user_id integer not null references users,
project_id integer not null references project_tracker_hours,
deadline_id integer reference project_tracker_deadlines,
notes varchar(4000),
-- start_time and finish_time include date and time
-- If total_hours is blank, we will assume that the
-- user precisely enter the times and will calculate total_hours
-- If total_hours is not blank, we will assume that start_time
-- and end_time represent "general timeframes" and not try
-- to recalculate total_hours
start_time date,
finish_time date,
total_hours number(5,2),
-- billing rate is in dollars
billing_rate number(5,2)
);
create sequence project_tracker_customer_id_seq;
create table project_tracker_customers (
customer_id integer primary key,
creator_user_id integer references users,
creation_date date
name varchar(200),
-- we are not going to bother storing a ton
-- of structure information
-- To store individual contact records, use the contact-manager
contact_info varchar(4000),
-- potential, current, past
status varchar(100),
notes varchar(4000)
);