Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Design Q: Normalizing a Personnel Table

Re: Design Q: Normalizing a Personnel Table

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 25 Jun 2002 00:50:27 +0200
Message-ID: <a98fhuo8ehomao2g5krfam2bbokms76r52@4ax.com>


On Mon, 24 Jun 2002 17:45:54 -0400, Rob Williamson <robw_at_physics.umd.edu> wrote:

>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
>

Evidently we are discussing 'subtypes' here. You already discovered there are 2 ways to implement them: in one single table with views, or in different tables, forcing you to combine them with UNIONs (preferably in a view) when you need to report them all.

As the number of attributes which applies for only one single subtype is relatively small, I would not use separate tables. I would also not use separate tables if I would know beforehand that I would need to report everyone in the far majority of cases. In your particular case I would choose one single table and views. If I would choose multiple tables, I would probably not go for a separate table with the 2 attributes common to all tables in a separate one, as you would need joins everywhere.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jun 24 2002 - 17:50:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US