Posted by onedbguru on 04/05/06 23:33

Before I go into a relational model of this a quick comment about your
current table definition.

for 'ss' (I assume this to be a social security number) it should NEVER
have a default value. You cannot have an employee without a SS#.
Period. I would make it NOT NULL but remove the default value. In
fact, I would remove most of the default values as most of this data
must exist for all employees.


Here is a classic example - the "employee" database - shipped with DEC
(now Oracle) Rdb
TABLE EMPLOYEES
Column Name
----------- ---------
EMPLOYEE_ID CHAR(5)
LAST_NAME CHAR(14)
FIRST_NAME CHAR(10)
MIDDLE_INITIAL CHAR(1)
ADDRESS_DATA_1 CHAR(25)
ADDRESS_DATA_2 CHAR(20)
CITY CHAR(20)
STATE CHAR(2)
POSTAL_CODE CHAR(5)
SEX CHAR(1)
BIRTHDAY DATE
STATUS_CODE CHAR(1)

TABLE JOB_HISTORY
Column Name
----------- ---------
EMPLOYEE_ID CHAR(5)
JOB_CODE CHAR(4)
JOB_START DATE
JOB_END DATE
DEPARTMENT_CODE CHAR(4)
SUPERVISOR_ID CHAR(5)


TABLE JOBS
Column Name
----------- ---------
JOB_CODE CHAR(4)
WAGE_CLASS CHAR(1)
JOB_TITLE CHAR(20)
MINIMUM_SALARY INTEGER(2)
MAXIMUM_SALARY INTEGER(2)



TABLE DEPARTMENTS
Column Name
----------- ---------
DEPARTMENT_CODE CHAR(4)
DEPARTMENT_NAME CHAR(30)
MANAGER_ID CHAR(5)
BUDGET_PROJECTED INTEGER
BUDGET_ACTUAL INTEGER

A view to find your current status:

table current_job
Information for table CURRENT_JOB

Columns for view CURRENT_JOB:
Column Name Data Type Domain
----------- --------- ------
LAST_NAME CHAR(14)
FIRST_NAME CHAR(10)
EMPLOYEE_ID CHAR(5)
JOB_CODE CHAR(4)
DEPARTMENT_CODE CHAR(4)
SUPERVISOR_ID CHAR(5)
JOB_START DATE VMS
Source:
SELECT E.LAST_NAME,
E.FIRST_NAME,
E.EMPLOYEE_ID,
JH.JOB_CODE,
JH.DEPARTMENT_CODE,
JH.SUPERVISOR_ID,
JH.JOB_START
FROM JOB_HISTORY JH,
EMPLOYEES E
WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID
AND JH.JOB_END IS NULL;

As you can see, as long as JOB_END is NULL - you are still working in
that position.

[Back to original message]