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.
