table design

[PMac]

I have to design a database that will track employee information. I have a
table I want to enter information about the employees. My problem is that I
have 3 different types of employees. Each will requie fields that the other
two do not. Do I create 3 different employee tables, one for each or do I
place all 3 types of employees into one table and leave the fields empty?

Thanks!
Pete

 

[renold1958]

Depends how many different fields you have for the employees, normally you
keep a table like employees together

 

[Tim Ferguson]

My problem is that I
have 3 different types of employees. Each will requie fields that the
other two do not. Do I create 3 different employee tables, one for
each or do I place all 3 types of employees into one table and leave
the fields empty?

You actually need four tables:

Employees( *EmpID, FullName, OtherStuffCommonToAllEmployees)

PartTimers( *EmpID+, MaxSessions, SessionsInHand, etc)

FullTimers( *EmpID+, OverTimeRate, ContractRevsions, etc)

Temps( *EmpID+, DateWhenFullRightsGranted, etc)



All employees get an Employees record, and the EmpID from this table
supports all transactions for them. They also get a record from exactly
one of the other tables, obviously taking their EmpID with them. The +
sign means that the EmpID is a foreign key referencing Employees, so that
you can't have a FullTimer that isn't also an Employee, and you can't
delete his Employee record until you've deleted the FullTimer record too.
Being PK to PK, it's a one-to-one relationship; this is one of the few
genuine reasons for such a thing.

Unfortunately, Access cannot guarantee the rule that you can't create a
FullTimer record at the same time as a PartTimer record, but as long as
you can control all data access via forms, then a little bit of code will
do it for you. If you have the chance to move up to a real DBMS (ahem!)
then you can use update triggers to enforce it.

This is quite a common technique known as Subtyping.

Hope it helps


Tim F

 

[PMac]

Thanks!

Depends how many different fields you have for the employees, normally you
keep a table like employees together

 

[PMac]

Thanks for your reply! I have done what you have said and am in the process
of creating forms (BTW, I am using HTML for this). I have run into a problem
- how do I get the Emp_ID from the Employees table into the PartTimers table
with and insert statement? I am entering an employee for the first time and
have the fields set up. Now, I have fields set up for the PartTimers table
but since I have Emp_ID as an autonumber in my access table, I don't know
what Emp_ID will be for this new record... What do I do?

 

[PMac]

Thanks for your reply Tim!

I have done what you wrote - creating 4 diferent tables. Also, I have set up
Emp_ID as an auto number in the Employees table, but how do I insert the
Emp_ID into the PartTimers table (all in one form)? BTW, I am using HTML and
Coldfusion not Access forms. Thanks!

 

[Tim Ferguson]

- how do I get the Emp_ID from the Employees table into the PartTimers
table with and insert statement? I am entering an employee for the
first time and have the fields set up. Now, I have fields set up for
the PartTimers table but since I have Emp_ID as an autonumber in my
access table, I don't know what Emp_ID will be for this new record...
What do I do?

Part one - depends on how you are allocating the EmpID number in the
Employees table (which you have to do first). If you are using ADO or DAO
it's easy:

rst.AddNew ' new record
rst!SomeField = Something ' fill in the fields
rst.Update ' save the record
dwNewEmpID = rst!EmpID ' in DAO at least, it's available as soon
' as the new record is dirtied. I think that
' in ADODB it's not until the record is
' saved, but I dont use ado much.

If you are using INSERT commands then it's harder -- the SELECT @@Identity
command is not guaranteed in a multiuser situation. Probably best to
allocate the number yourself.

Part two -- allocating it to the PartTimers table is easy, just plug it
into the INSERT statement:

strSQL = "INSERT INTO PartTimers (Emp_ID, etc, etc2) " & _
"VALUES ( " & Str$(dwNewEmpID) & ", " & etc

' do put this line in until you know it works properly!
MsgBox strSQL


Hope that helps


Tim F