Relationship Hell

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.

Posted By: Donald Belcham

Published at

Comments

No comments posted yet.