In late 2007 I started a project that required a pass-through database to allow for auditing and change approval to occur between our CRM product and Outlook. I was, and am, acting as the database administrator (DBA) on this project. I was fortunate enough to be able to create the database almost single-handedly.
Doing everything yourself is tedious work. It can also go very wrong if you assume that you're not just doing it alone, but doing it for yourself. I stuck to some best practices to create cleanly formatted, self-documenting, and commented SQL code. This has allowed me to have exchanges like this:
Coworker: "What is the procedure to get a contact's information?"
Me: "getContact"
Coworker: "Oh."
Coworker: "What if I want to add this user as a subscriber?"
Me: "Use the addSubscriber procedure, pass it the contact ID and the user name."
Coworker: "Oh."
Eventually they stop asking because almost everything is so predictable. These exchanges may make them feel ridiculous but that's the point. If you stick to a well defined naming scheme then your code should do a lot of self-documentation. If the code is documented then it will normally be equally convenient to simply reference the code. This is a benefit to everyone. There is less need to bother me. The code is more readily available to the other developers than I am anyway.
Another benefit became apparent to me a few times recently: It helps you find when you are about to do the same thing twice. This is a big database that my team has been working on for the last year. There are almost 200 stored procedures and functions. Unfortunately, I was not able to create them all. I did create the vast majority, though, sticking to my same naming conventions each time. Now we are actively developing parts of this project again, so I need to put new functionality in the database. At least twice in recent weeks, a procedure I was prepared to create already existed, was named exactly what I intended to name it and did exactly what I wanted.
The lesson here is the power of doing things the right way for the sake of it truly being the right way. I could have probably done things faster if I didn't take the time to properly indent my SQL queries or if I used the query designer every now and then. Yet, I would cost myself and the company time in the long run because it would be far more difficult to decipher those queries to troubleshoot or change them. I could have used procedure names that were less descriptive to save some typing time or avoid names such as "getAcceptedCompanyMinorityStatusValues" but then I couldn't tell you exactly what the procedure does without looking at the code, and if I needed that later I might use a different naming scheme and do the same work twice. It is usually quicker to do it right once than it is to do it half right twice, especially when twice is likely to grow exponentially.
