Database design - Field is only applicable when another field has certain code (2NF)

Question

I have the following table design:

 TABLE: WORK_ACTION_CLASS 
 WORK_ACTION_CLASS_ID VARCHAR2(24) Primary Key
 NAME VARCHAR2(64)  64 Action Name
 ROLE_CLASS_ID VARCHAR2(24) Role Class ID That Performs This Action  
 CHECKLIST_CLASS_ID VARCHAR2(24) Checklist Class PK  
 WORK_ACTION_TYPE_ID VARCHAR2(3) 

Where the WORK_ACTION_TYPE_ID is a simpe lookup

 1=Done Button
 2=Dynamic Checklist
 3=Custom Form
 4=Progress Log
 5=Approve/Decline Button  

When the Action Type is a Dynamic Checklist, a CHECKLIST_CLASS_ID is required so that the action knows the specifics of a Checklist to use programmatically. I don't like this design because if this Action Definition is not a Dynamic Checklist, CHECKLIST_CLASS_ID field is not applicable. I don't know the best way to separtate this fact out.

So it might be correct to say that my that my table is 2NF not 3NF. If so, how do I or should I try to get to 3NF??

Answer 1

You can add a table for dynamic checklists.

create table work_action_dynamic_checklists (
  work_action_class_id varchar2(24) primary key,
  work_action_type_id varchar2(3) not null
    default '2'                                  -- ???
    check(work_action_type_id = '2'),
  checklist_class_id varchar2(24) not null,
  foreign key (work_action_class_id, work_action_type_id) 
    references work_action_class (work_action_class_id, work_action_type_id)
);

For that to work, you need to add a unique constraint on {work_action_class_id, work_action_type_id} in work_action_class. This guarantees that rows in this table always reference a row in work_action_class that has work_action_type_id = '2'.

But unless you implement other changes, too, you lose the ability to require a checklist_class_id for every row that has a work_action_type_id of '2'. (A CHECK() constraint can fix that in the original table.) You could attack that problem with triggers or updatable views.