Should I further normalize my current design to eliminate redundancies?
I have the following tables:
empl: standard user table
empl_cat: employee "category"
empl_admin_cat: administrative "category"
Both of the category tables have a many-to-one relationship back to the
employee category represents the categorization of the employee within the company.
administrative category represents that employee's administrative oversight (if applicable) over other employees.
The tables are defined like this (irrelevant columns have been omitted):
empl ( empl_id int PK, ... ) empl_cat ( empl_cat_id int PK, empl_id int FK, eff_date date, base_id int FK, eqp_id int FK, pos_id int FK ) empl_admin_cat ( admin_cat_id int PK, empl_id int FK, co_id int FK, base_id int FK, eqp_id int FK, pos_id int FK )
I considered using a single table with a
type column, but decided against it because they logically represent two different things, and while 3 of their columns are the same (
pos_id), they each have one additional column (
co_id). Was this the right decision?
Would it be worthwhile creating another table that has an autoincrement Primary Key which holds all possible variations of
pos_id, with the
empl_admin_cat tables referencing the appropriate record in this new table instead of having their own
pos_id fields represent their "base of operations", "operating equipment", and "position". For example, SFO/777/Captain. The
co_id in the
empl_admin_cat represents a company.
empl_cat is an effective date.