Archives

Orphaned Records and Technical Hypochondria

I found more orphaned records recently when querying a M2M table with an order by clause. Once again, this occurs because in my opinion the M2M database is not properly designed. Here’s a quick walk-through on how I found it. Incidentally, the screen shots are from TOAD using M2M’s practice database.

A manager tasked me with cleaning up our customer and address tables which consists of looking for real world customers having multiple customer numbers in M2M. When looking into the address table, I used a script similar to:

 SELECT fcaliaskey AS [CUST NO]
,      fcaddrkey AS [Add Key]
,      fccompany AS [Company]
,      fccity as [City]
,      fcstate as [State]
,      fczip as [Zip]
FROM M2MDATA66.dbo.syaddr
WHERE fcalias = 'SLCDPM'

The reason I use SLCDPM is because I am looking for customers, if I was trying to clean up vendors the fcalias would be ‘APVEND’. Anyway, that script returns a dataset which looks like this:

With the exception of some missing zip codes, the data set looks fine. To simulate orphaned records I artificially introduced some blank fcaliaskey records using the following script:

UPDATE M2MDATA66.dbo.syaddr
SET fcaliaskey = ''
WHERE fcaddrkey = '0003'

Incidentally, the fcaliaskey field should never be blank, but I found several records in my production database that were. Anyway, we receive the message that 4 rows are affected. However, if you re-run the query above, the dataset (that you can see on screen) looks the same. It’s only after you apply an Order By clause that you find the problem.

Oops


Now for those who write SQL, this is not earth shattering information, but there’s a reason I’m sharing it with you.

How Do You Deal With Technical Hypochondria?

What? Who?


Above I described how I found more orphaned records recently when querying a M2M table with an order by clause. Technically this is data corruption, but when people hear that term they tend to panic.

The Manager I was dealing with is what I call a technological hypochondriac. He is an old school accounting type who doesn’t trust those d*mned computers. He keeps paper records for everything, saves his adding machine paper tapes, spends weekends at work double checking even insignificant numbers, etc.

So, you’re the DBA. You’ve found orphaned records, records that cannot possibly be used in your ERP system because the field which would have linked them, in this case the customer number, is empty.

What do you do?

Related posts:

7 comments to Orphaned Records and Technical Hypochondria

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>