I’ve been working with the software and the data structure at my new
job for a week now and, although some things about the job are nice,
today placed me right in the middle of a relationship quagmire. The
data structure is ID based, relational and, for the most part,
normalized. Today I started writing queries for the first report to be
built in my newly created data driven reporting structure. I chose to
build a very simple product listing report first to find glaring errors
in my new code as quickly as possible.

So this report is three columns of data; code and description fields
from one table and a code field from a parent table. I thought that
this would be a simple enough query with one join between two tables,
but I was oh so very wrong. It turns out that the data architect has
decided that the Foreign Key fields should allow NULL values. Because
of this, the INNER JOIN syntax normally be used will not return the
records with the NULL values in the Foreign Key field. The only way
that I’m going to be able to successfully write any query in the
application will be to use the LEFT JOIN syntax all of the time.

Why would someone do this, I ask? If a child record has no related
record in a parent table, why not create a record, with its own ID, and
make it a special record for ‘NONE’? Perhaps there are other ways to do
this, but certainly using NULL in a Foreign Key field will cause
endless grief in the application and, perhaps more-so, in reporting.

Thankfully I found this only one hour before quitting time and I didn’t
have to fume over it for the entire day. I did however decide to have
my own sit-in to protest this design, and I successfully did nothing
for that last hour of work.

May Boyce, Codd and Booch punish this data architect daily.