I’m, um, writing this for a friend. Yes, that’s it.
A friend. Or maybe a friend of a friend. We all know this friend, though. I’m sure you have one. Anyway, Jeff’s (b|t) topic reminded me of a story my friend was just telling me, and it seemed like the perfect time to pass it on.
So, my friend was working on a project for a nursery who supplied landscaping companies. Another flavor of the software her company supplied was for orchards. As such, there wasn’t much place for plant information, just tree information. There was an EAV table though that allowed for many custom attributes to be added. So the customer had set up attributes for the plants in an order in the EAV table.
The customer then purchased the nursery supply sofware, and my friend got tapped to help the customer … smooth the way from how the data was imported into the old version and tie into the newer version. There were two additional tables with the nursery supply version of the software, so my friend thought she could write a trigger to push values that came in from the import through that EAV table to rows in the correct new tables. (Even though triggers make her shudder.)
This sounded pretty easy until she discovered a problem: all the reports were tied to the EAV table, and the customer expected any changes in the new tables to push back to the EAV table so the reports would still work. Yipes! And of course, the EAV table was one of the busiest in the database. Additionally, this forms a horrible circle of trigger looping hell.
My friend was very clear to both her project manager and the customer in question that she felt that this was a poor idea, and that this could have a significant negative impact on the database. She agreed to develop it though and put it into a UA environment for the customer to test thoroughly. The final solution ended up being fiiiive new triggers, 4 sanity brakes, three staging tables, two tables from the new version of the software, and a job calling a stored procedure. Oh, and the original EAV table. (Phew.)
Each set of triggers that populated any of the staging tables checked backwards to see if the record came from the previous staging table(s). If it did, it ignored it and didn’t continue to push the record forward. The job was for the final push from the EAV staging table into the EAV table, to avoid many incremental inserts/updates and instead do those in a batch.
Although the final verdict of this whole mess hasn’t played out at this time, both my friend and their customer weren’t very confident that this was going to be a success. There were additional issues such as data not being able to be definitively mapped, questions about how the data was imported into the EAV table, etc. I’m pretty sure I know what the endgame for this project is. Well, there are a few different varieties but all of them resemble a train wreck.
For all of you out there currently working on one of these, my friend and I feel your pain. Best of luck!
