All Things Patrick - Working Hard to be Lazy!

St Joseph, Mo, Take 2, HJB, beginning of December to beginning of January -

I finally found another job that would give me the excuse to get away from the previous job. The consulting agency that had found me the job down in Tulsa had found something for me up in St Joseph, about 1.5 hours north of Kansas City. They gave me the url for the website and I wasn't sure if they were for real.

I wanted to be sure of what they wanted. It looked liked Ma & Pa had done it themselves, hired a nephew to do it, or something similar. It looked like a business reject site from the mid-90's back when nobody had any idea what they were doing. I was wondering if they handled enough business to even be able to pay me and if they were able to pay, would they?

I was told that they realized that their site was not what it needed to be and needed a complete redesign. They had a bunch of different websites and all of them needed work. They needed somebody to completely take over their websites - they wanted a webmaster. At that point I was willing to talk with them. On the 1 hand I was kind of excited to be completely in charge and on the other hand I was kind of worried.

The company got it's start when the owner had a mental break down and was sent off the psychiatric ward. He had a choice of basket weaving or jewelry making - he wasn't going to do basket weaving - so that left jewelry making. It was really relaxing and helped him a lot and turned into a hobby. Him and his wife started taking their jewelry to various shows - at first small shows and as they made more money they eventually went to larger shows. He figured that if he could do this and make money at it, anybody could!

He ended up making a bunch of DVDs on how to make jewelry and made a lot of money on them. They paid the high schooler down the street to make their website and from there the business just grew. People asked him where to get jewelry making supplies and he decided he owed something to the people who had bought his DVDs so he started adding jewelry supplies. I'm not sure what all the reasons where, but at some point they decided to move from the little town in Alabama and move up here to St Joseph, Mo. They ended up being able to get more orders out with less people than they had down in Alabama, they started offering more products, and business got even larger.

They had multiple sites, but their big money maker was the store front for their warehouse of products. Their 2nd biggest money maker was a web mall where people could rent space and sell the jewelry they made. The other sites made some money, but not as much as these 2. Everything had literally been written by the high schooler down the street.

The best way to describe what probably happened is to use an anology. They needed shelter so they built a shack - it worked and was declared good. They needed more space so they built another shack attacked to the first. Over time they built another shack and another, then added a lean-to. They then wanted a 2nd floor so built another shack on top of the 4 shacks and lean-to below. It simply continued in this manner and at no time did he step back and look at it. At some point I would think that he would have realized that he needed to stop and rethink, refactor, and rebuild. At some point you need to realize you need to tear down the mound of shacks and build yourself a house - preferably prior to the entire thing collapsing and causing a loss in business.

My first week there I got to participate in counting inventory. Usually programmers never get the chance to participate and rarely get the chance to see how users really use the system. I got an up close and personal view.

Before I even touch the code I want to say that about 45% of visitors leave this site within 3 seconds of landing on the front page. Somewhere around 60%-70% leave within something like 10-15 seconds (it's been a while since I was able to look at the stats). Both AWStats and Google Analytics were pretty close on these 2 numbers so I'll assume they're fairly accurate.

---

Certain things made me think the entire thing had been coded for php3. Everything was an include() - not a single require(), include_once(), or require_once(). A second query was performed to determine the number of returned rows in the result set rather than utilizing mysql_num_rows() or utilizing count() or sizeof() functions.

Other things pointed to his general lack of understanding of things. He had used HTML comments to comment out PHP code - the cpu expensive PHP still got processed even though it wasn't wanted. In a lot of places each item had it's own file which simply set a variable and included another file - this may be ok when you have 5 products, but when you end up with 10,000-20,000 PHP files that are only 1 liners this is not easily organized - especially once you realize that there are also images, text files, pdfs, and other files in that same directory! I had somebody ask me what I meant by a 1 liner - this would be the contents of /items/A2-215.php -

<? $itm="A2-215"; $sb=""; include("../items.php"); ?>

Ok, why not use /items.php?itm=A2-215 instead? It would do the exact same thing without requiring an extra read to the hard drive (not to mention yet another file to keep track of!). The problem with coming back and cleaning it up later is that before you can get rid of the code that creates the /items/A2-215.php you have to search through the code base for every possible way that could create a link to this file and change it to point to /items.php?itm=A2-215 instead. Once that's finished then you could get rid of the file creation code and hope it didn't break anything else.

Any time he needed a database connection he include()'d his connect.php, changed the database he needed to connect to if necessary, and then proceeded to make SQL queries. Never once did he release the database query. This could be done 2 or 3 times in the same file (each time he needed to switch databases) - not to mention that each file he include()'d might include() this connect.php file multiple times as well!

connect.php
<?
if (!preg_match("/specific-directory/i", $PHP_SELF) || $session[username] != "")
    $dbh=mysql_connect("localhost", "user-name", "p@ssw0rd");
?>

---

Getting past the actual PHP code, things were poorly organized on the drive itself. Let's considering /contestblah/ (besides the display.php, display1.php - display27.php, and display-p1.php - display-p3.php PHP files) it also has lots of image files in it. Depending on when it was done, some of these may be thumbnails. All of these kinds of directories also have a resize/ in them that has a resize.php file (it's the same resize.php, just copied to every single /*/resize/ directory) and all of the resized thumbnails.

The web root / has resizemain.php, resize.php, resize2.php, resize3.php, and resize4.php. The /cgiimages/ directory doesn't have any of the resize*.php files, but it does have resize/, resize2/, resize3/, and resize4/ directories. I know there was a couple of directories in which not only did it have the original image, but also a thumbnail, a resize directory with a thumbnail, and then sometimes that had a subdirectory with yet another thumbnail.

---

I'm not sure what thought process (if any?) went into the creation of the database. There was a database for affiliates, applications, clickbank, customers, faq, links, products, support, and surveys. No, these are not individual tables - the main website used 9 different databases! Ok, I can understand if you start off with different databases because of mergers, had each major department own their own database, or had certain parts of the database become to large and you decided to place parts of it on a separate server (or even the same server, but had different databases on different physical hard drives). Customer data was duplicated across multiple databases and data that should have been placed in the same table was shoved into some other table it had no business being in.

The absolute best example I can give of how well this database was normalized is to describe how the orders were kept - in one single table.

Normally at the very least you'd want 1 table to define the order - the order number, customer id, date the order was placed, tax, total shipping cost, etc. Then you would have a 2nd table for the order's list items (or products). This 2nd table would store the product SKU, the quantity being ordered, the price paid (especially if your sale price changes regularly or other factors may change the individual item price and you either have no way to easily recalculate this or you don't want to waste cpu cycles recalculating it), etc.

Instead, this cart table (stored in the Customers table, not the Products table) has a text blob field labeled items. This text blob was a CSV text field and typically looked something like this -

"X1-109","0.99","3","Some sort of Product Name", "A2-98","0.99","1","Some other Product Name", "X1-128","0.99","5","These Product Names Could Probably Even Be Considered a Short Description", "X1-321","2.75","1","Yet Another Product Name", "G16-4","6.95","1","Nifty Product!", "X1-101","1.50","1","Larger Than Normal Product - Pack of 3", "XD2-9","10.00","1","Some Product - Pack of 10",

All this information is crammed into 1 field of an order row. To the uninitiated this may seem ingenious - it saves space and there's not an extra table to mess with. Unfortunately dealing with text blobs can become cpu and memory intensive to deal with. They can also cause a lot of disk thrashing with a lot of reads and writes as you try to resize 1 in the middle of a huge table. For those that don't know: Text Blobs don't have a set size - they resize themselves to the content given - depending upon the database server and the filesystem, changing the contents of 1 of these could cause a lot of resources to be utilized. Text blobs have their uses - descriptions, articles, and such... but using them for content that could be better stored in a seperate table is a waste. Yes, cpu cycles and memory are cheap, but hard drive space is even cheaper and easier to get and add to.

Had this data (the items) been stored in a separate table they also could have been queried by the database. Stored as they were, in order to calculate how much you sold of a particular product so far this month you would have to do your normal query to pull orders for this month. Unfortunately instead of being able to join that to your list items table and query based on sku (which the database is optimized to do this) - you would then have to pull every single order out of the database (cpu and network intensive) and parse through it's items field (cpu intensive) searching for the product your looking for.

Rather than completely killing the system in this manner it had a script that ran every day at midnight (along with every other script set to run at midnight) that stored every product it came across in an array and totalled quantities for the day (cpu, network, and memory intensive!). This information was then stored in the best sellers table.

The kicker is that when the order was placed it wasn't immediately removed from inventory. Instead there was a script that would run every minute and check to see if there was an order that had yet to be processed. If it found one it would begin processing the order... Which included pulling out the data from that items field, parsing the SKU and quantity out of it, and removing it from the inventory table (which was in a completely different database - the Products database).

This caused multiple problems unrelated to the workings of the actual system. Several times someone would place a test order or a call in order from a customer and then they would freak out when it wasn't immediately removed from inventory. I had to explain to them several times that this wasn't instantaneous. If you had 5 people place an order during the same minute it would take at least 5 minutes for all 5 orders to be placed. That's assuming there were no network time outs and the MySQL server was always instantly available to process the request.

And yet another layer of processing was involved when the pickers got the order information for what needed to be picked. They scanned the order print out bar code and then the bar code on each item they picked. Once they were finished picking I believe they had to scan the order bar code again - at which point a comparison had to be done to determine whether anything was missing, wrong items had been picked, etc. (Un?)Fortunately I was never able to get around to looking at the code that was required to do this... Considering the rest of the code and the fact that they stated that this had caused a lot of problems, I don't even want to think about what this hack job looked like.

---

The server itself was just a mess. It was using Exim as the email server and I don't know of anyone that's had success with Exim under high volumes of email. The Tip of the Day went out to over 60,000 email addresses and the Weekly Newsletter went out to over 70,000. Add to that the fact that the owner attaches his email address to every piece of useful and junk software, ebook, and whatever else he can attach it to. At that point the email farmers lap it up and run it through every variable name generator they can get their hands on so they can spew spam across the entire domain. Then consider this poor Exim server is handling this amount of spam for not just 1 domain, but for about 10 active domains and over 150 other domains that simply point to 1 of the active domains.

The filesystem on the server consisted of about 7 different partitions of various sizes. Each and every one of them was a software raid partition consisting of a single disk - how this worked I honestly don't know, but that's extra processing on top of everything else. I do remember that the partition the spool was on was always full... This also doesn't help Exim deal with flood of emails it was trying to handle.

---

Even though they had 3 different reseller accounts the mall, the next biggest money and traffic generator, utilized the same server as the main site! I think the mall had the most unrealized potential as it was complete crap and yet it still made money. Granted, it didn't need to make it's client's any money - it generated money by renting space.

It generates enough traffic to generate just enough income for a majority of the clients. This website, however, generates about 70% of the trouble tickets for the entire company. Considering that and the hosting expenses I'm wondering if they're even breaking even on this.

To begin with, when a new client signs up for a store, if they don't do things absolutely correctly the store creation gets screwed up - if they don't file a trouble ticket there's no way to know that the store needs to be fixed. Fixing a newly created store requires the programmers to do a complete deletion of the existing store and re-create the store following the same steps the customer did only following the correct proceedures instead of adding the little quirk that caused the creation to fail.

Once the store has been successfully created there is a difficult process in order to actually set up the front page and start adding products. This usually generates yet another trouble ticket requesting help. Luckily anybody familiar with the process can explain it.

The store is created and products have been added. Now a customer comes along and decides they want to buy 5 of your products. Unfortunately a customer can only have 1 item in their cart at a time... They'll have to go through the order process 5 times in order to buy 5 items from the same store - talk about dedication!

Not only that, but the mall has no concept of quantity. It doesn't store any quantity amount anywhere. If you only have a quantity of 1, you'll have to manually remove that product after it's been purchased. Not only that, but if you have 5 people order that same item you only have 1 of, you'll have to refund 4 orders - not fun.

Now a client hasn't sold anything, is completely annoyed with the interface, and just wants out. There's no easy button on this 1 either because it's done via PayPal's subscriptions... only within the mall it's not called PayPal subscriptions, but by a completely different name (which I don't recall). Client's usually generate 1 or more trouble tickets over this and it seems the initial trouble ticket is sent off to the programmers... Who, of course, can't do anything about this because it's not something that the mall can handle. The client has to login to their PayPal account, find the subscription in their past billing, and discontinue it.

Due to the obfuscated naming of it within the mall and the unhelpful description of what needed to be done in the manual form letter there was 1 customer who sent an email off to PayPal asking PayPal to do something (this was unclear - stop payment, refund money, something else?) and PayPal's reply was that PayPal couldn't do anything, that was the responsibility of the mall. So the client followed up on the mall trouble ticket and basically got the same response so went for her lawyer - she was going to sue! Apparently most people don't take the time to listen or comprehend what they're being told (or what they're reading?) so their request for help is incomplete. Because the person being asked for help is also not listening/comprehending or maybe they're new on the job and don't know what's going on replies to the request with incomplete data. Thus we devolve into a he said, she said situation and nothing is accurate.

I did a simple search on PayPal for subscription and the first thing that popped up in the FAQ/Knowledge database panel on the right was information on how to remove yourself from a subscription. I sent the link off to the person handling the issue, but after that if you can't figure it out it's a PayPal issue and not something the mall can do for you.

---

At one point all of the cronjobs had disappeared and we couldn't figure out why the site wasn't processing any orders. We called the guy who had previously worked somewhat with the highschooler to see if he could help us - he was the 1 that discovered the problem with the cronjobs. We had to restore all of the cronjobs (about 15-20)... We restored the 1 that processed orders first and waited for it to catch up before adding the others - and they also had to catch up.

There were several other times where the processorders.pl script disappeared from the cronjobs list, but all of the other scripts were ok. A couple of times the only way to fix the website was to restart the MySQL server or remove the backlog of emails in Exim. These are things you don't really want to have to do on a normal basis in a production environment.

The 2 weeks surrounding christmas was a daily flipflop between just fix it - buy quick books or whatever we need and just forget and let it die, we'll put together a subscription site. Finally the decision was made to just let the main site and the warehouse sites rot while they built the subscription site and hoped that it generated enough revenue before the other 2 completely fell apart. Because they decided to go with a commercially hosted membership site (http://membergate.com/) they no longer needed a developer and let me go.

---

What frustrated me the most wasn't the fact that they let me go, but that they decided to let 2 completely viable (and currently limping along) businesses to just rot. Especially considering that I could see the potential in both. Their main site (with the warehouse of jewelry making supplies) did over $3 million in 2007.

I think the mall site, even with all of it's problems, averages about $2,500 a month. With a complete overhaul I think both customer and client frustrations could drop to next to nil. More people would be willing to sign up and less people would be quitting. After the complete overhaul, if they continued paying as much attention to it as they did (just answering trouble tickets) there would be very little time required to maintain the mall site.

I hope that my final email to them explained my thoughts -

hey,

From the start I think Home Jewelry Business has 2 great website ideas (with the very important business system) with the potential to make lots more money than they currently make... Just not the way their websites currently exist.

To make an analogy you have a Yugo with 2 flat tires & a leak in the radiator. The Yugo is not broken - at least as long as you continue to put water in the radiator you can turn the engine on without problems & you can get it to limp along down the street... Granted it grinds the rims, but it works, but no where near what you need.

Considering the traffic you get... & just think, about 45% leave after less than 3 seconds according to awstats! Just think about what would happen if you actually got that 45% to stick around... and the amount of inventory that goes through the warehouse... What you need is an 18-wheeler (a semi or tractor trailer rig) in order to have something that can handle your business.

Putting duct tape over the holes in the flat tires & putting bubble gum over the hole in the radiator just isn't going to get the Yugo to where you need it. It may help it limp along until the replacement is ready for production. As it is the suspension is getting trashed, the other 2 tires are about to blow out, & the engine is burning up just trying to create enough torque to move it.

Please find attached a PDF "staysane.pdf". I should have tossed this book into the mess when I first started so I apologize. If you don't have the attachment or don't trust it, then please download it from the website. Jonathan Oxer, an Aussie (Australian), wrote a book called "How To Build A Website And Stay Sane" <http://www.stay-sane.com/> that's targeted at clients of development firms & developers... Basically the people who need to read it are the people who are either just starting to build a website or those who are needing a rebuild. It helps them to set realistic expectations & understand what is involved.

I'm disappointed in your decision to fire me, but you seem determined to let your current business rot as you concentrate on building your new membergate.com membership site. As I stated in my email this morning, membergate.com is not designed for developers so if you are looking at replacing me with another developer then I suggest you be honest with them & tell them you don't need their development skills, but are really interested in their understanding of copy & paste for membergate.

Other than me trying to figure out Matt's convoluted spaghetti code, looking at what I did while at Home Jewelry Business I suggest that what you need is not a developer. What you need instead is someone with a lot of hardware/networking and Windows support experience that is interested in doing some web stuff. Or you could look at hiring 2 interns - 1 that does the hardware, networking, and windows support & the other that can do some simple web stuff.

I suggest that you read "How To Build A Website And Stay Sane" before you attempt to hire another developer. It will also help you in dealing with Jason's company and understanding what kind of real expectations you should have of them.

Patrick