5

I had very carefully asked the table designer why he had chosen to create CSV columns in multiple tables.

The designer's answer was that splitting CSV columns into separate tables was "unnecessarily complicated".

One CSV column is used in an implicit join to count how many times a value in the multitude of values in the CSV column has been used.

The where cause that binds the two table uses a like % | table_b.csv_column | % operation.

I read and learnt that normalisation is not always required if there is a good reason not to.
However, I don't know what could be good reasons for not normalising.

Is this (avoiding "complication") a good reason for not enforcing first normal form (1NF)?

TABLE: TB_DISASTER_CAUSE_CATE

CATEGORY_ID   CATEGORY_N CATEGORY_L L
------------- ---------- ---------- -
DC006001002   ****                3 Y
DC006001003   ****                3 Y
DC006002001   ****                3 Y
DC007001001   ****                3 Y
DC007002001   ****                3 Y
DC007002002   ****                3 Y
DC007003001   ****                3 Y
DC007003002   ****                3 Y
DC007003003   ****                3 Y

TABLE: TB_DISASTER_HISTORY

SEQ DISASTER_TYPE DISASTER_CAUSE                                             
----- ------------- ------------------------------------------------------------
   32 DT001003002   DC001001004|DC002001002|DC003001001|DC007002001             
   33 DT002003007   DC001001004|DC007002001                                     
   34 DT002003009   DC001001003|DC003001002|DC007002001                         
   16 DT002003001   DC001001004|DC002001002|DC005001003|DC007002001             
   17 DT002003001   DC001001004|DC002001002|DC005001003|DC006001003|DC007002001 
   18 DT002003007   DC001001003|DC001002002|DC002001001|DC002002002|DC004001001|
                    DC007002001                                                 

   19 DT002003007   DC001001003|DC001001004|DC003001002|DC007002001             
   20 DT002003007   DC001001003|DC001001004|DC007002001                         
   21 DT002003007   DC001001003|DC003004001|DC007002001                         

  SEQ DISASTER_TYPE DISASTER_CAUSE                                             
----- ------------- ------------------------------------------------------------
   22 DT002003007   DC001001003|DC001001004|DC002001002|DC007002001  

QUERY

select 
          x.category_id as category_id
          ,x.cnt as ccnt
          ,y.category_nm as category_nm
        from(

        select category_id , count(*) cnt from 
            TB_DISASTER_CAUSE_CATE  a , 
            tb_disaster_history b 

           where last_yn  = 'Y'
             and b.disaster_cause like '%'||a.category_id||'%'
             and b.disaster_type=#disaster_type#
         group by category_id  ) x

         inner join (
         select * from tb_disaster_cause_cate
         where last_yn='Y') y
         on x.category_id = y.category_id;
  • Could you provide the schema for the table in question and some sample row values? Are you saying the designer told you they created CSV columns that held multiple values because it WAS unnecessarily complicated to do so? Or because NOT to do it was unnecessarily complicated? Did they provide more rationale than that as "complicated" is a pretty relative term. – Todd Everett Dec 8 '15 at 2:03
  • @ToddEverett I've updated my post. Thank you. – Ascendant Dec 8 '15 at 2:26
7

The answer to this question is the same as the answers to this question. Both are really about first normal form (1NF). The linked question had to do with justification for a key column made up of different data elements concatenated via a dash "-". This question has to do with justification for a non-key column made up of different values of the same data element contented via a pipe "|". To answer, we must first assess if such a design violates the intent of 1NF, and second, if so, when would it be justified to make that violation of 1NF anyway.

Background

First, a quick re-cap of what constitutes 1NF. A table is a relational (R-Table) table, and thus normalized (meaning in 1NF by definition) if in its design a discipline is followed that ensures:

  • Distinct, un-ordered rows
  • Uniquely named, un-ordered columns
  • Each column contains a single value from the corresponding domain

Note requirement 3 implies no column can have a missing value (or a NULL marker). Let us assume for the moment that requirements 1 and 2, and the "no missing value" part of requirement 3, are met. I say "assume" because we really cannot assess compliance without seeing all the rows in the table to verify there are no missing values, no duplicate rows, and no implicit information encoded in the ordering of columns or rows. Now if the schema included integrity constraints which implement the business rules from the conceptual model to guarantee no duplicate rows and no missing values, we would not have to examine the all the data to verify compliance with respect to those requirements as we know the DBMS will enforce consistency with those rules in every case. We would still have to examine all the data however to ensure row or column ordering is not being used to implicitly encode additional information.

Given the assumptions just set forth, we have left to determine if the placement of multiple disaster cause codes into a single column separated by pipes violates requirement 3 for single value columns. In my answer to the previous question about a key column with multiple data elements separated by a dash I said such a design did violate that requirement, and that under no circumstances would it be justified. My answer is based upon study of the most current and definitive thinking on 1NF from Fabian Pascal and Chris Date (references to which I will provide at the end of the answer). Fabian himself contributed to answering that question with a correction to my answer. He said my assertion was too strict, and that:

If the combination is just for visual purposes and it is guaranteed that it will always be accessed by the DBMS as a single value (emphasis mine here). The problem is that such guarantees are shaky.

Fabian's correction strikes at the heart of the subtlety of the single valued column requirement. If that column's domain is a relation-valued domain (RVD), and a single table thus contained in that column, it would not be a violation of 1NF. Before we rejoice in the ability to now justify the practice of your "senior" dev in creating columns with multiple data elements separated by dashes or pipes or whatever else, I want to first point out that no SQL DBMS actually supports RVDs, thus making operating on the column as a single value difficult. Second, both Date and Pascal point out that while an RVD is not a violation of 1NF, it does introduce additional complexity and asymmetry without (except in a few extreme circumstances) any additional benefit. So while it may be permissible, it may well not be a good idea. Please refer to the references for the details on this.

Evaluating the Design

With the background on 1NF in place, let's get down to this situation. The column in question, disaster cause, I would submit is not a single value column precisely because you give an example query where individual values within the larger string are accessed. Therefore the table is not normalized. Given this, is it still justified to design the table in a such a way as to make it not an R-Table simply because the normalized design calls for two tables and is thus perceived to be "unnecessarily complicated?" I would say absolutely not for two reasons. First, Einstein said things should be as simple as possible, but no simpler. This un-normalized design is an example of "too simple" as by having a table that is not an R-Table none of the benefits of an R-Table can be gained. The references go into detail on this point, but the main point is that guaranteed logical access to each data element is lost. A more subtle issue is that maintaining the integrity of the disaster cause is much more difficult. If the disaster cause were a child table, ensuring each disaster cause code can only be assigned once per disaster is guaranteed by the key constraint. As a multi-value column in the disaster history table an application could easily accidentally concatenate the same cause code twice. It makes my head hurt even contemplating how to write the procedural trigger that would be necessary to implement this constraint on the multi-value column. Finally, note I have addressed only the logical design. I am sure there are many experts in the physical implementation of various SQL DBMS' who would cringe at the resulting optimization of the queries that will be run, like the one provided, to access this data.

Lastly, I would argue that even the rationale that a normalized design with two tables is "unnecessarily complicated" is wrong. Instead, the multi-value column in the single table is in my opinion "unnecessarily complicated." The query you provided, which is trying to count up the number of times a given disaster cause contributed to a disaster, is much more complicated to write than it would be if the design simply had a disaster table and a child disaster cause table. The nesting would be unnecessary and the join would be simple and obvious as to its purpose. Someone looking at the schema for the first time can easily understand, without even seeing the data, that we have information about disasters, and each disaster can have many causes. With the multi-value column this simple structure is obscured and the first time user would have to dig into the data values to discover that in fact a disaster can have many causes.

References

Fabian Pascal's Practical Database Foundation Series. Paper #5 addresses 1NF and RVDs. If you purchase all the papers (which you really should as they are a complementary set addressing all the fundamentals) two additional papers can be obtained that specifically address 1NF - "What First Normal Form Really Means" by CJ Date, and "What First Normal Form Means NOT" by Fabian Pascal. Next, CJ Date has written extensively on relational theory. The most current reference with respect to normalization is Database Design and Relational Theory: Normal Forms and All That Jazz. Finally, Fabian Pascal has blogged recently about 1NF and those blogs are excellent reading.

  • 1
    One of the best answers I've received in all the SE sites. Thank you for your elaborate answer and the references. A big one upvote ! – Ascendant Dec 9 '15 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.