3

When creating documentation for a project, I understand some things are completely necessary, however I was asked to provide a brief description for every query in a system I built. That's over 100 queries.

If unfamiliar with queries (SQL), it is language used to interact with databases. Update/delete/retrive data/add records, etc. It can be tedious and quite convoluted.

The business' case is that it would assist in cleaning up the system of unused/repeated queries.

They are all so specific to a particular process/report that if a Developer came into the system and looked around, they would be able to tell what they do due to their naming conventions and the clarity in the scenarios they are used.

I don't understand why a business user would need this documentation? I am trying to not act like I am just being lazy.

It's not like they should have the ability to go in and delete a query if they don't think it's doing something properly.

I have never heard of documentation being that detailed. To me, it's the same thing as going through each line of code and telling the user what it does. It seems like a very ignorant request, probably with good intentions, but still ignorant.

What can I say back to the user? Or is this a reasonable request?

  • Why not put comments in the create scripts? Just a quick statement of what the query/view/function/proc does. It is a pain after the fact, but if you start doing it now then all future items will have it, saving everyone a lot of trouble. – Dave Johnson Nov 12 '14 at 15:04
  • @DaveJohnson I do! The code is VERY well documented. – Mark C. Nov 12 '14 at 15:04
  • Then you do not need to write new descriptions. Just compile them. Busywork for sure, but not a huge deal. If management is asking, I would just do it. – Dave Johnson Nov 12 '14 at 15:06
  • @DaveJohnson Got it. It seems like more of a 'mapping' queries to business tasks than anything. I'm just not sure what use the business would get out of it. – Mark C. Nov 12 '14 at 15:08
  • If something was worth encapsulating in a query then it's worth documenting. – TheMathemagician Nov 12 '14 at 15:12
1

Our database has thousands of queries, so I would hate to be asked to do this. SQl code is basically self-documenting to any dev who understands the database structure. Documenting what different tables do and what the fields in those tables means seems more usefuul to me than explaining every individual query.

However, since you have been asked, this is what I suggest. Take one particulary complex query and write the documentation. Then take two similar queries that hit the same basic tables but are different based on the fields they need to return or the where conditions and write those up.(This is show them why code reuse is often not possible if you want efficient SQL even when the basics of what you want are similar.) Then send these write-ups to the business and ask them if this is the level of detail they want. Once they see how less than useful it is from these examples, they may back off. Or they may tell you exactly how little they want which will save you time on all the others if you still have to do it.

BTW do prioritize this so the ones that are the hardest to understand from just reading the the code (the ones with the "why would you do that?" explanations needed) are the ones you do first. These are the most likey to have some utilty, so get them out of the way first.

Another approach might be to categorize the different business needs and note which queries or stored procs might be in which category of business tasks. This might help a new dev who needs to do a financial report to see what queries are currenly querying the financial tables, so he can see if one of them can be reused or used as a starting point. So you could have a list of what queries are used to get people datails, what are used to get order details, what are used to get financial information, etc.

| improve this answer | |
  • Really - a great take on my question. Your first paragraph hit the nail on the head and summed my dilemma right up. I'm thankful you are seeing things from the perspective of queries and their functionality. – Mark C. Nov 12 '14 at 16:56
6

Not sure this is the case, but it sounds like you have no documentation. A brief description of what a query (or any other custom object in your system) does and more importantly why it is built that way has more benefit that just a laymen's description of your code.

Knowing the purpose of code, the "why" is important when change requests are made. This is how you catch conflicting business rules among different departments.

Although this is a part of "cleaning up the system of unused/repeated queries" it's not enough. Suggest monitoring your system (I know it has a performance over-head) to see what is actually being used. You may have to run it during heavy periods like month/quarter/year end. Many databases track the last time and the frequency certain objects were used.

There are other ways to scan your sql statements to see if there is some duplication. I don't think you'll find many that are exact duplicates unless someone is doing a lot of copying and pasting because they assumed the code would be modified.

If you already have adequate documentation, you can refer to it and then try other strategies to accomplish what they want. You've been given a problem along with a solution. Dig deeper to find the real problem and offer alternative solutions. They need to be aware that every change request on a query will require an adjustment to these documents. They may not like the push-back that is to come. Give them what they need, not just what they want. They'll thank you in the long-run.

| improve this answer | |
  • 2
    The first two paragraphs are really important. Documentation explaining what basic code does is pointless at best and oftentimes inaccurate as code changes then are coupled to the actual documentation. But adding "this does (weird something) because (department X) needs (weird something)" types of documentation are priceless. – enderland Nov 12 '14 at 15:04
  • Great answer. A couple notes: The system has a fairly-good amount of documentation (about 20 pages worth). The only thing that isn't 'documented' per se, is the SQL. Do you have a minute to chat? – Mark C. Nov 12 '14 at 15:07
  • I was only referring to the sql for not being documented since it didn't seem like you had even one sentence on each. Sorry, couldn't chat at that time. – user8365 Nov 13 '14 at 2:20
1

The documentation that they have requested will allow people to know what the system does without having to read the source code.

The current state of the project and it's functionality is likely to differ from what it was when first conceived and specified. That's if it there was a specification to begin with. Either way, it can be helpful for those concerned if there is something that describes what the system does in detail. That could be the current developer in six months time after they have forgotten the details, a new developer that's just starting or even someone not directly involved with development such as a project manager.

Documenting the system in this way can also help you to understand it more intimately and to identify problems and areas that can be improved.

| improve this answer | |
  • The only thing I can think of to comment on your answer is that the system does exactly what the requirements specify. It follows a strict business process. There is nothing 'new' in the system that the business doesn't know what it is doing. They don't know the code, nor should they, but it does task X just how task X is supposed to be done. If that makes sense? – Mark C. Nov 12 '14 at 14:36
  • 1
    @Jimmy If the specification doesn't include details about specific objects such as stored procedures or specific queries, then I can imagine that they would want to know what each stored procedure does so that they can hand that over to other developers to read up on. That same documentation would be useful to you also. But if the specification does actually state, stored procedure X does Y, or that query X does Y, then I can't see any point in you having to describe that stored procedure X does Y, or that query X does Y, because the specification already does that. – Alex Nov 12 '14 at 14:46
1

I dunno. You built the system, so obviously you think that both the naming conventions and the clarity of your codebase explain everything. I'm sure your code isn't as good as my code, which I feel that the gods themselves look down and smile upon.

I'll tell you I know my code is great, because I've never read anybody else's code that is even half as good as mine! When i read other people's code - typically undocumented! - it is soooo annoying.

...

In the scenario where they save money by hiring a cheaper dev team, or where they expand, your initial notes will possibly provide some use. They're paying you to do this, and the way you write it up makes the task sound super easy (you can tell what the sql does, there are a 100 or so items) so why not just do it and keep them happy?

Maybe they got burned before by someone (not as good as you or i) who wrote just insane code and the people brought in next had no clue what was going on or why!

IF you really don't want to do this, then write up 10 or 20 of them, and show them to the people requesting it. Ask them to confirm this is the kind of documentation they want. They might see your point from reading this. They might not though.

EDIT

A caution - you're just a developer, so you don't get to make statements like "it's not like they should have the ability to go in and delete a query if they don't think it's doing something properly". The business people get to decide business scenarios, the developer just does the work asked.

As a developer, you typically get autonomy on how you architect and design and code the system ~ but if the business want to mitigate risk, they're not going to listen to your advice on the matter, because that is now a business domain issue.

| improve this answer | |
  • In no way do I view my code that way, and I apologize if I gave off that vibe. But when I named a query SelectAllEmployeesHiredInCurrentYear I think that is a fairly straightforward naming convention. I do think you hit on a couple good points - but the business should never get into the code and make changes/deletions. – Mark C. Nov 12 '14 at 14:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .