question related to database normalisation

[pepesmith]

Sir/Madam:

I am developing a database for our Call Recording System as one of
my project.I have designed the following tables.

Employee - table that contains all of the Employees
which will recieve the call on that particular extension/local number
if you call the number
** Though the employee fields contains a lot of fields here are the fields which were expected to
be accessed by my program:

EmployeeID -> Primary Key
Lastname
Firstname
MI
DepartmentID


ExternalUser - table that contain all of the User which are not considered as Employees
yet they are considered User of a particular extension of local number since they
reside on same compound and vicinity and their telephone units
are connected to our current PABX setting

The fields of this table are:
ExternalUserID ->Primary Key
ExtenalUserDescription

I've found that it is really necessary to separate these two groups since we have already
an existing employee table in our SQL Database Server . My task is only to select a certain employee
on that table and save that into a separate table.

My question is :
How could I make a normalize table out of this one? I have tried to make a separate table
and named as TelephoneUser with fields :
- TelephoneUserID(the primary key)
- UserID
- isEmployee - bit or flag field which determines whether a user is an Employee

but i've found out that If EmployeeID & ExternalUserID is of the same ID or value i have
to resort in checking the isEmployee field. I believe this type of approach doesn't follow
with the expected normalisation proccess. Would mind suggesting me what is the proper way of
handling this type of situation?

Thanks in advance.


Pepe Smith Jr.

[zipperboy]

Two ways that I can think of, to fix your problem:

1) Stick an additional digit in front of the EmployeeID, which will indicate if it's an employee or not.

For example, Employees get an "8" and non-employees get a "9".
So,
Joe Employee - EmployeeID 1011 becomes 81011
Joe Contractor - ExtrernalUserID 1011 becomes 91011

This will only work if you can put enough digits in to ensure that the EmployeeID does not roll over into the extra digit (ie, if the number of employees becomes 80000 you have a problem with the above example. You may need to add several extra placeholder zeros before the extra digit).

2) Make your new table key a composite key of EmployeeID and the Employee/Not-Employee Flag field.

Dave

[pepesmith]

thanks buddy..