I just started working with a new client in Port Charlotte. They have had a website built by a company in Tampa and how now parted company with that company, so to speak. So I pick up this system and start to go through the coding and it’s build etc, trying to get up to speed as quickly as possible. But something is not right. It’s a struggle, confusing, big, messy. I don’t know why…

 Well I eventually figure out that this website system, asp.net code, classes and modules, forms, and by golly the database and stored procedures have, for the most part be generated by a …. MACHINE! ARGHHHH.

Yes, thats right, the original developers are working with a software tool that allows them to work in plain english (I guess) with a client to spec out the system. They do their high level analysis etc, enter data into the software, press a button and voila! Here is your website sir!

This doesn’t include the design of the site aesthetically, the way it looks etc, which is actually quite nice…

So, what’s my point. Here it is… A machine generated system (i’m going back to database design days years ago when I used software tools to design databases) tends to be broken down into a very low level. What I mean by that is the mathematics and algorithms that do the analysis and create the design tend to go WAY TOO FAR! This means that you end up with a system that is very complex. It explains one reason why my new client decided to jump ship from the pervious developer. The initial development costs where quite low compared to the cost of ongoing development and support. The original developer ended up in the same position as I did. After producing the system with a software tool, they were left looking at it, pondering its complexity and wondering how it all works exaclty.

I’m currently working on a project that requires posting and reading response online using XML. I’m not talking about web services here, it’s a plain old POST of an XML file, response is returned as copious amounts of XML, read that and build a web form based on the contents (yawn), have website user make selections and POST that back as plain old XML, get another response back as, yep you guessed correctly, XML and read that, make a decision based on the contents and voila, we’re done.

 I’m a little bit surprised that the company that runs this service, the bowels of which I am groping around in, has not developed a SOAP API or Web Services API that can be programmed against without the raw manipulation of XML (i.e. text) data. I’ve used the Amazon, PayPal and EBay API’s and while there is a hefty learning curve it is far more intuitive, in my humble opinion, to code against an API than against raw XML.

Whenever I start having to parse text files, I don’t care if we have XSD files and a schema, yada yada ya, I feel like I’m using old technology that is cumbersome, although it does the job, right. I know that the legacy of XML and the use of text goes back a long way, when it was far more difficult, time consuming etc to open up a port and configure a firewall, so passing text through was an easy solution.

I can only guess that this company has so much legacy code and systems that the move to a SOAP API and web services infrustructure is still in the pipeline. Just as there are still tons of companies that still use EDI right?

I’m currently working on a project where we are importing data transactions on a daily basis and need to compare the imported file with the current file. One of my favorite SQL clauses is the NOT IN clause, which I have used many times to do comparison type queries. Quite often, this powerful clause will help to simplify a system so much that its almost unbelievable.

This is how it looks;

SELECT DISTINCT
 resort,
 account,
 seqno,
 amount,
 pcode,
 ptype,
 pdate
FROM dbo.revenueMaster_import
WHERE dbo.revenueMaster_import.rowKey NOT IN(
 SELECT DISTINCT
  dbo.revenueMaster_archive.rowKey
 FROM
  dbo.revenueMaster_archive)
ORDER BY dbo.revenueMaster_import.seqno asc

This query shows all of the distinct rows from dbo.revenueMaster_import that are NOT IN dbo.revenueMaster_archive. There are many ways you might need to compare data like this. I’ve created many systems that utilize this technique to help keep a systems table structure to a minimum and therefore reduce a systems complexity.

Happy memorial Weekend!