What's the best way to design this?

Hello,
I am desiging a database that will host information about employees. The
problem is that there are about 5 different types of employees (hourly paid,
etc) and each of them have different information that is stored for them.
I am wondering whether to have a huge table to handle the different fields
(on average about 25 fields per type) , or have an employee table to host
basic info (name, address, phone etc) and then split the employee types into
different tables and have a foreign key link to each of the employeetype
tables. Is there a better way to do this?

Scared of the concept of a 75 field table,

John.

There will be some fields that are common to all/most of the types, e.g.
Surname, FirstName, BirthDate, Gender, Address, City, Zip, Notes, ... These
fields at least belong in the Employee table, with an EmployeeID primary
key.

Some of the other fields might find into a related table, e.g.:
EmployeeTypeID - one of the 5 values;
PayRate - currency

It may be necessary to have 5 related tables handling these fields if they
are radically different, but my guess is that you will be able to shoehorn
these various fields into this related table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Access rookie" wrote in message
...
Hello,
I am desiging a database that will host information about employees. The
problem is that there are about 5 different types of employees (hourly
paid,
etc) and each of them have different information that is stored for them.
I am wondering whether to have a huge table to handle the different fields
(on average about 25 fields per type) , or have an employee table to host
basic info (name, address, phone etc) and then split the employee types
into
different tables and have a foreign key link to each of the employeetype
tables. Is there a better way to do this?

Scared of the concept of a 75 field table,

John.

Thanks for your help Allen; I will check for all common fields and then get
the related tables set up.

Have a Happy New Year.

John.

"Allen Browne" wrote:

There will be some fields that are common to all/most of the types, e.g.
Surname, FirstName, BirthDate, Gender, Address, City, Zip, Notes, ... These
fields at least belong in the Employee table, with an EmployeeID primary
key.

Some of the other fields might find into a related table, e.g.:
EmployeeTypeID - one of the 5 values;
PayRate - currency

It may be necessary to have 5 related tables handling these fields if they
are radically different, but my guess is that you will be able to shoehorn
these various fields into this related table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Access rookie" wrote in message
...
Hello,
I am desiging a database that will host information about employees. The
problem is that there are about 5 different types of employees (hourly
paid,
etc) and each of them have different information that is stored for them.
I am wondering whether to have a huge table to handle the different fields
(on average about 25 fields per type) , or have an employee table to host
basic info (name, address, phone etc) and then split the employee types
into
different tables and have a foreign key link to each of the employeetype
tables. Is there a better way to do this?

Scared of the concept of a 75 field table,

John.