Module 3

1
Module 3 Database Structure
Workforce Information Database Training Last
update November 2006
2
Module 3 Database Structure
3
Module 3 Database Structure
WORKFORCE INFORMATION DATABASE LOGICAL DATA MODEL
FOR LONG-TERM EMPLOYMENT PROJECTIONS
4
Module 3 Database Structure
Two Considerations of the Workforce Information
Database Structure

• GEOG and SUBGEOG tables (refer to handout)
• State-defined field values

5
Module 3 Database Structure
Updates to Workforce Information Database
Versions

• Reasons to update
• Keep current (new data tables added or
• changes in source data)
• Address needs of the states
• Make the database structure responsive
• to customer needs
• Correct errors

6
Module 3 Database Structure
Two Types of Workforce Information Database
Releases

• Interim Release
• Done on as needed basis
• Correct omissions, errors
• Try to implement only changes that have
• no or little effect on existing customized
• software
• Changes are voted on by the
• Analyst Resource Center

7
Module 3 Database Structure
Two Types of Workforce Information Database
Releases

• Full Release
• Changes can be more substantial than in
• the interim release
• Changes in full release can have more
• impact on custom software developers
• Draft of proposed changes are delivered
• to states for review and comment
• States can submit comments and
• suggestions to the Analyst Resource Center

8
Module 3 Database Structure
Two Types of Workforce Information Database
Releases

• Full Release - continued
• Each comment and suggestion is
• examined by members of the
• structure subcommittee
• Changes may be made based on the
• comments and suggestions
• Final draft version of the structure is
• submitted to the Analyst Resource Center
• for vote
• Version is released to states, along with
• supporting materials

9
Module 3 Database Structure
Workforce Information Database Releases Formerly
ALMIS Database

• Two Full Releases
• Version 1.0
• Version 2.0
• Four Interim Releases
• Version 1.1
• Version 2.1
• Version 2.2
• Version 2.3

10
Module 3 Database Structure

• Depending on your
• states implementation
• and use of the Workforce
• Information DB,
• you may not be working
• with the most
• current version!

Survey Says Approx. 96 have Version 2.3 in
development/test environment and 79 have Version
2.3 in production environment
11
Module 3 Database Structure
Versions 2.2 and 2.3 have Triggers
12
Module 3 Database Structure
Versions 2.2 and 2.3 have Triggers
13
Module 3 Database Structure
Trigger Example
Soccode table
Action Insert new soccode 959595
Trigger fires!
Occcode table
Action Automatically inserts new soccode
959595
14
Module 3 Database Structure

• Why have the Occcode
• Indcode tables?
• Can not have conditional foreign keys as
• was happening in the case of the multi-code
  fields in many of the Workforce Information DB
  tables.
• Can not have a foreign key field length
• that is different than the primary key
• it is referencing.

15
Module 3 Database Structure
Where to go for help Workforce Information DB
Expert E-mail almis_at_ncmail.net Phone
919.733.2936 www.workforceinfodb.org Click on
Helpdesk National Crosswalk Service Center
(NCSC) www.xwalkcenter.org E-mail
ncsc_at_iowa.gov Phone [phone] Or contact a
fellow Workforce Info DBA
?