Data Modeling, Breaking & Fixing First Normal Form (1NF) - Page 2

Mistake 2, internal array element

This second form of breaking 1NF takes the form of creating a column that contains many values of an attribute. Taking from our CITY table example, this mistake makes our CITY table look like the one in Listing 5. This is a much worse form of breaking 1NF than our first mistake. It has the added difficulty of no boundaries for columns in the city except for the comma separator. It is prone to errors in data entry around the comma-separated fields. Sometimes we will encounter spaces before or after the comma that can play havoc on any parsing that we may want to do.

Listing 5
City table with internal array structure

Since there are no columns and we are not quite sure of the number of cities that any one column may or may not have, our only solution to this problem is to create some logic (application) to extract the information from this column. In Listing 6, there is code to make this mistake behave just like a normal table. In order to accomplish this task we need to do the following.

1. Rename the CITY table. This is done since we are going to want to keep access to this data through the same object name of CITY.
2. Create a few abstract data types for manipulating our output.
3. Create a function that will be called when requesting information from the CITY table
4. Create a view called CITY.
5. Now you can just select directly from the CITY view.

Listing 6
Making an internal array listing behave like normal rows

RENAME city TO city_tb
/
CREATE TYPE CITY_TY AS OBJECT
       (STATE  CHAR(10),
        CITY   VARCHAR2(100))
/
CREATE TYPE CITY_TY_TB AS TABLE OF CITY_TY
/
CREATE OR REPLACE FUNCTION CITY_FC
         RETURN CITY_TY_TB PIPELINED IS
         PRAGMA AUTONOMOUS_TRANSACTION; 
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
out_rec      city_ty 
          := city_ty(NULL,NULL);

vcity     VARCHAR2(100);
vstartpos NUMBER;
vendpos   NUMBER;
vlastpos  NUMBER;
BEGIN
OPEN cur0 FOR 'select state,city,instr(city,'','',1),instr(city,'','',-1) from city_tb';
LOOP
  vstartpos := 1;
  FETCH cur0 INTO out_rec.state, vcity, vendpos, vlastpos;
  EXIT WHEN cur0%NOTFOUND;
  IF vlastpos = 0 THEN
    out_rec.city := vcity;
    PIPE ROW(out_rec);
  END IF;
  LOOP
    EXIT WHEN vlastpos = 0;
    select instr(vcity,',',vstartpos) into vendpos from dual;
    IF vendpos = vlastpos THEN
      out_rec.city := substr(vcity,vstartpos,vendpos-vstartpos);
      PIPE ROW(out_rec);
      out_rec.city := substr(vcity,vlastpos+1);
      PIPE ROW(out_rec);
      EXIT;
    END IF;
    out_rec.city := substr(vcity,vstartpos,vendpos-vstartpos);
    PIPE ROW(out_rec);
    vstartpos := vendpos+1;
  END LOOP;
END LOOP;
CLOSE cur0;
RETURN;
END CITY_FC;
/
CREATE OR REPLACE VIEW CITY AS 
  SELECT a.state,a.city
  FROM TABLE(CITY_FC) a
/

The breaking of 1NF can and does cause havoc in all of our lives. Until you have the time and resources to fix the problem within the physical model, you must provide solutions that are easy for everyone to use. My suggestion to you is to come up with a solution that will work and allow you to fix the real problem with the least amount of impact on your user community. If you take an approach to put a view in place of the real table, let your user community select from that view, you will be able to hide the logic behind the scenes and hopefully one day fix the real problem and allow yourself to get rid of the view and underlying logic.

» See All Articles by Columnist James Koopmann