Database Reference
In-Depth Information
INSERT INTO hr.sms_carrier_host
( sms_carrier_cd, sms_carrier_url ) VALUES
( 'Sprint', 'messaging.sprintpcs.com' );
INSERT INTO hr.sms_carrier_host
( sms_carrier_cd, sms_carrier_url ) VALUES
( 'Verizon', 'vtext.com' );
Creating a Table of Employee Mobile Numbers
We already have a table in the
HR
schema that includes the e-mail address for personnel: the
EMPLOYEES
table. However, we also need to store pager numbers and cell-phone numbers for employees, so we will
create a table to hold those data values, the
emp_mobile_nos
table. We need one more data value to tie all
this together: when a user log's in, and we identify her through our SSO process, we need a way to
associate the
HR
EMPLOYEES
and
emp_mobile_nos
data with the user. We will add a column named
user_id
to the
emp_mobile_nos
table to make that association. See Listing 9-7 for the creation command.
The
HR.EMPLOYEES
table includes a primary key index on
EMPLOYEE_ID
, which is an independent
numeric value: each employee that is hired is assigned the next numeric value, sequentially. Whatever
that value is, we will use to assign mobile numbers to that same employee. We are further associating
that numeric
EMPLOYEE_ID
with an SSO logged-in user, the
user_id
column.
Listing 9-7.
Create a Table for Employee Mobile Phone Numbers
-- Adjust length of Pager_No and Phone_No as needed
CREATE TABLE hr.emp_mobile_nos
(
employee_id NUMBER (6)
NOT NULL
,
user_id VARCHAR2(20 BYTE)
NOT NULL
,
com_pager_no VARCHAR2(32 BYTE),
sms_phone_no VARCHAR2(32 BYTE),
sms_carrier_cd VARCHAR2(32 BYTE)
);
At this point, I will point out an important fact that you need to consider. As we go forward in our
security structures and procedures, we become dependent on the security that we established
previously. Our two-factor authentication depends on SSO; we need to identify the user who is
requesting two-factor authentication in order to send the two-factor code to the correct devices.
Additionally, we need to know what
EMPLOYEE_ID
is associated with a
user_id
. Two-factor
authentication can only succeed for employees who have an entry in the
emp_mobile_nos
table
, or an
alternative. Consider the possibility that we might have added these columns directly to the
HR.EMPLOYEES
table. It would have been correct to do so as far as data normalization standards dictate;
unless it can be proved that not all employees need access to our applications.
It may seem a bit backward to have the computer
user_id
field in a table of mobile phone numbers
instead of in the primary
EMPLOYEES
table, but we are adding this functionality onto the existing
HR
structures, and we decided to do it all in one place, the
emp_mobile_nos
table.