Certification Module.

re: 11/23/03


The ct_certification module will contain the information that is to be tracked concerning the CERTIFICATION and CERTIFICATES that are earned/received by the fire fighters, etc.

The CERTIFICATION relationship diagram is depicted below:
CT ER

Table Definitions:

A summary of the table definitions depicted in the ER diagram above, is listed below.  The complete definition of these tables can be found on the Max Mart, Inc. project web site and will be included in the final CT documentation.

There are two major themes modeled by these tables including CERTIFICATES and CERTIFICATIONS.

CERTIFICATE Information:

The CL_CERTIFICATE table stores the certificates that are earned by people in the system.  The CT_ISSUE table stores the dates that the individual certificates were issued.  This allows a single certificate to be renewed when it expires.  CT_CERTIFICATION is a table that stores the certifications that the MFS offeres to the members.  The certification can be of a specific CT_TYPE.  The CT_EQUIVALENCE table logs the equivilant certifications that are offered by other agencies (CT_AGENCY).

Table: CT_CERTIFICATE

Description:  The certificate level that is earned. This has the certificate number that doesn't change when the person recertifies.
NAME TYPE WIDTH NULL DESCRIPTION
PS_PERSON_ID NUMBER 10 NOT NULL The person who earns this certificate level.
CT_CERTIFICATION_ID NUMBER 10 NOT NULL The certification level that this persons earns.
CERTIFICATE_NUMBER VARCHAR2 30   The number of the certificate SEAL that is stamped onto the original certification. This number will be printed on subsequent certificates that are renewals for the same level.
EARNED DATE 0 NOT NULL Date of initial certification at this level.. This may also be in the other table - certificate_issued - but may want it to be here for easier reference.
RENEWED
 DATE 0
Date of renewal of this certification
EXPIRES DATE 0
Date the certification expires
STANDARD VARCHAR2 20
The standard of certification at time of issue


Table: CT_AGENCY

Description:  The other agencies that issue certificates.
NAME TYPE WIDTH NULL DESCRIPTION
ID VARCHAR2 5   User provided ID UPPER CASE
NAME VARCHAR2 100 NOT NULL The name of the certification agency.
CONTACT VARCHAR2 50   The name of the contact person for that certification agency.
PHONE VARCHAR2 13  
SY_ADDRESS_ID NUMBER 10   ID that references the SY ADDRESS table. An agency can have only ONE address.


Table: CT_CERTIFICATION

Description:  The levels of certification i.e. fire fighter1, fire fighter2, instructor1, instructor2, etc. A Certification is a combination of the NAME of the certification and the CERTIFICATION_TYPE_ID. Ex: Fire Fighter 1 - Equ Fire Fighter 1 - Std
NAME TYPE WIDTH NULL DESCRIPTION
ID NUMBER 10   Generated ID for the certifications.
NAME VARCHAR2 50 NOT NULL The name of the certification level.
DESCRIPTION VARCHAR2 200   A descriptive detail of each level of certification.
CURRENT_STANDARD VARCHAR2 20   The CURRENT STANDARD of this certification level. Ie: 1992 standard, 2001 standard, etc. This will be copied into the individual's Certification to know what the standard was when they received certification.
CT_TYPE_ID VARCHAR2 3 NOT NULL The certification type for this certification. This might be COMPLETE or EQUIVALENCE, etc.


Table: CT_EQUIVALENCE

Description:  The different names that the agencies call the certification levels like fire fighter1 might be BEGINNING FIRE FIGHTER from the Boston agency.
NAME TYPE WIDTH NULL REF DESCRIPTION
CT_CERTIFICATION_ID NUMBER 10 NOT NULL CT_CERTIFICATION (ID) The certification level that is equivalent to the certification.
CT_AGENCY_ID VARCHAR2 5 NOT NULL CT_AGENCY (ID) The certification agency that provides the certification equivalence.
NAME VARCHAR2 100 NOT NULL   The name of the certification equivalence that this agency offers to match our own certification.


Table: CT_TYPE

Description:  Holds the types of certifications i.e. equivalancy, or complete
NAME TYPE WIDTH NULL DESCRIPTION
ID VARCHAR2 3   User provided id. TRIGGER for UPPERCASE!
NAME VARCHAR2 30 NOT NULL The name of the certification type.
LOGO BLOB 0   The logo that goes with each certification name. This will not show up over the NET on a web page unless we make it a LONG RAW.