Follow @DBDebunk Follow @ThePostWest

Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.

Q: “Does anyone have an alternative solution to the issue where multiple nulls are allowed in the same column for a unique index containing that column? I have a number of such nullable columns and unique index combinations in my database schema and I'm trying to avoid having to create additional generated as columns for each case.”

A: “null<>null so the problem's built into unique columns, perhaps the columns need to become non-nullable?”

“Some attributes in a tuple are not meant to be part of any relationship so people are safe to populate such relationshipless attributes with a Null when needed. Just an example, how about the famous MailAddressSecondLine attribute describing the optional second line of a mail address? You can populate it if needed or you can leave it with no value a.k.a. Null if you have nothing to put in there.”

“So, when YOU create an address table, do you have a "second address" line or do you normalize it? Enquiring minds want to know.”

“You are asking how I would implement optional parts of an address within the limitations of a fixed set of numbered address line attributes? If an address has fewer lines than there are attributes then I'd populate the extra lines with zero-length strings. Null wouldn't be appropriate because all the parts of the address are known and present and using null would give undesirable results. For example if you compare all the parts of an address in a join or subquery you ought to expect two identical addresses to be returned as equivalent, but that wouldn't work if you start adding nulls into addresses.”

We have discussed extensively the problems with and solution to the treatment of missing data in relational databases:

------------------------------------------------------------------------------------------------------------------

SUPPORT THIS SITE
DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.

LATEST POSTS 

06/19 PREDICATE LOGIC, SEMANTICS AND RDM (sms)

05/28 INTENSION, EXTENSION AND R-TABLES (t&n)

05/08 ON PROPERTIES & CHEN'S E/RM (rm)

UPDATES

04/23 Added The Story of Mathematical Proof to LINKS page

04/03 Added First OrderLogic to LINKS page

04/03 Added Mathematical Logic - Reasoning in First Order Logic to LINKS page

03/26 Added Modeling of Integrity Constraints Dependencies to LINKS page

03/14 Added Russell’s On Denoting to LINKS page

03/14 Added Russell’s Paradox to LINKS page.

LATEST PUBLICATIONS (order from PAPERS and BOOKS pages)

08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

USING THIS SITE
- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my AllAnalytics columns no longer work. I re-published only the 2017 columns @dbdebunk, and within them links to sources external to AllAnalytics may or may not work.

SOCIAL MEDIA
I deleted my Facebook account. You can follow me @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.
------------------------------------------------------------------------------------------------------------------

Fundamentals

• NULL Value is a Contradiction in Terms

Misconceptions

• Many-valued Logics and NULL Part 1
• Many-valued Logics and NULL Part 2

SQL NULL

• Read my Lips: If There's NULLs, It's Not Relational
• Missing Data: RDM vs SQL -- A Real World Comparison

Alternative "solutions" (dropped)

• Missing Data -- Horizontal Decomposition Part 1
• Missing Data -- Horizontal Decomposition Part 2
• Missing Data and Multi-Relation Query Results

Relational solution

• The Final NULL in the Coffin: A Relational Solution to Missing Data

and we will not repeat all of that here -- we will just debunk the specific misconceptions in the above quotes.

With respect to the first quote, missing data is a logical, not physical issue and should be decided on logical, not physical grounds. While non-nullable columnsis the best that can be done in SQL, they do not do away with all problems, as SQL can generate NULLs on its own. Missing data will treated properly without NULLs only by a true RDBMS, rather than imposed as a user/application burden, particularly with respect to interpretation of results.

With respect to the second quote, it has nothing to do with normalization, which refers to elimination of relation-valued domains/attributes (RVD/RVA). The suggested solution is a neat application work-around for just printing out addresses. The relationally correct DBMS solution is, of course, two types of address entities -- with and without a 2nd attribute. For the design and constraints see Meaning Criteria and Entity Supertype-Subtypes Relationships.