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

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

Answers


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.


Need Your Help

Add field for each file in fine-uploader

custom-fields fine-uploader

Trying to implement Fine-Uploader for first time.