Design Q: Normalizing a Personnel Table

We are creating a Database that will have a Personnel table. We are a University so we have several types of Personnel. This will not be a huge database 1000 to 2000 records max. Probably more like 500 records in reality.
We have three types of people Faculty,Staff,Student. Within these groups there are two subsets Salaried Staff and Hourly Staff and Undergraduate and Graduate student.

To Keep this question simple we are proposing either one flat table for all of the above or breaking the tables down into their groups. For simplification of my question consider only our Staff hourly and salaried problem:
Proposed Flat file
Exempt is a Yes/No and describes whether an employee both Hourly or Salaried can get overtime,
Shop is the shop they work in Electronics or Machine Shop, The rest are self evident ( Salary Anual )

Personnel ( PK ID, Name,Exempt,Shop,Hourly_Rate,Salary,Fringe_Benefits)

We proposed breaking these down into 3 tables

Pesonnell (Pk ID, Name)
Hourly (FK ID,Exempt,Hourly_Rate,Shop)
Salaried(FK ID,Exempt,Annual_Salary,Fringe_Benefits)

This creates the poblem that Exempt in both tables will not allow us to denormalize.
Placing the exempt field in the Personnell table causes an empy field for Faculty and Students who are never exempt so we decided on the following:

Pesonnell (Pk ID, Name)
Shop_Emloyee(FK ID,Exempt,Shop)
Hourly (FK ID,Hourly_Rate,)
Salaried(FK ID,Annual_Salary,Fringe_Benefits)

All the FK are to the Pesonnel table ( Its not clear whether we need PK of some sort on each of those tables ?)

I feel like with the max potential size of this table being small over the next 5 to 10 years like we are over thinking this and that a flat table would be sufficient. Unfortuanately I don't have tons of design experience and my partner seems to believe their might be reasons other than the wasted space problems relating to Data integrity if Data entry is put in the wrong place for one group of Pesonnel. My feeling is that we can have a flat file and then create VIEWS of the individual types of Personnel and that way protect the fields we don't need for any given type of Personnel. Remember we will also have Faculty, Undergraduate, Graduate personnell and I worry that we might need to write queries based on 3 or 4 of those tables and therefore have to denormalize and is it worth it for 1000 rows?

I realize this is rather long winded but we lack the experience to make a decision
and would love some advice. We hope to create a nice robust Database that does not
have to be redesigned a few years down the road due to our innexperience.

Also any good sourses that discuss these matters would be great I found nothing on Oracle Metalink or Technet.

Thanks in advance if you have the patience to read and reply.

Rob Received on Mon Jun 24 2002 - 16:45:54 CDT