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?
First I would complain to M2M, not that it would do much good. I would then clean up the data as requested. I would not inform the manager unless he specifically asked me about what I found or if I was concerned that the corrupt data was very important.
Where did you get that picture? It looks like South Park.
To answer your question, are addresses that important anyway? I would just delete them, but if it was something related to accounting I would raise it with the manager.
Yes, it’s from the “Something You Can Do With Your Finger” episode of South Park. I chose it because the mall manager is so confused and paranoid, and I have a penchant for sausage and cheese. heh
I’ll search invoicing history for the customer name, if no invoice found just delete the records, if found, find a manager that is not “technological hypochondriac” explain the situation and fix the record!
[...] I mentioned last week, I came across a problem with orphaned records in my addressing tables. Rather than address my [...]
[...] Play the Orphaned Records Game In the past couple of weeks, I have discussed the problems of orphaned records in the M2M Database and promised to provide a [...]
Southpark is quite funny but some of the scenes maybe a bit too morbid even for adults.’”-