Tuesday, April 14, 2015

Documenting Your Tables and Columns in Oracle

If you ask most developers if they would prefer to get a root canal or write software documentation, most would choose the root canal.  Almost everyone universally hates to write documentation.  But in many cases, at least some amount of documentation for a system is essential.

This is very much true for the tables and columns in your database.  At some point, someone is going to need to know what data is stored in your database, what assumptions you made and what some non-intuitive column name really means.  Ideally, yes, we would like every table and column to have a meaningful name that was descriptive enough such that our database was self documenting.  But we all know this is rarely the case.

In Oracle, you can use the COMMENT ON command to put comments on tables and columns.  The exact syntax is shown below:

-- Setting comments on a table
COMMENT ON TABLE <<table name>> IS ‘<<comment text>>’;

-- Setting a comment on a columns
COMMENT ON COLUMN <<table name>>.<<column name>>
    IS ‘<<comment text>>’;

To add comments in this way, you need to either be the schema owner on the object or have the COMMENT ANY TABLE privilege.

It is pretty simple, the comment text that you enter will be stored as a comment with the table or column respectively.

Where is this comment stored?  It is stored in Oracle's data dictionary and you can access it through two different views, all_tab_comments and all_col_comments.  (You can also use the user or dba version of these views as well).

SELECT * FROM all_tab_comments;

SELECT * FROM all_col_comments
    WHERE table_name = ‘<<table name>>’;

By themselves, these views only provide the comments.  But you can easily join these views to other views in the data dictionary to get a more complete view of what is going on in your database.  Take for example the following query which joins the user_tab_columns and user_col_comments views in order to list all of the columns for a table with their data type, if the column is nullable and any comments entered for the column.

SELECT tc.column_name, tc.data_type, tc.data_length,
    tc.data_precision, tc.data_scale, tc.nullable,
FROM user_tab_columns tc
LEFT OUTER JOIN user_col_comments cc
    ON tc.table_name = cc.table_name
    AND tc.column_name = cc.column_name
WHERE tc.table_name = ‘<<table name>>’
ORDER BY tc.column_id;

Now, you have not just the column name and whatever data in the column to go on, you potentially have comments left by who designed this table or added this column about what their intent for this column was.  If you have ever taken over supporting an application and its database that has been around for a while, you know how useful this can be.

Success Factors

So that is the mechanics of how to add a comment for a table or column.  But how do we use this to solve the question posed in the beginning, the fact that everyone hates doing documentation.  Here are some tips:

  • DO focus on commenting tables or columns whose meaning is not immediately obvious.  Focus on columns that have cryptic names and the parts of your database that are difficult to understand.  Maximize your time by spending it on places where concepts or intent is not obvious and people may trouble understanding a year down the road.
  • DON'T comment every column just to say you have every column commented.  We all know what the purpose of the FIRST_NAME column is.  Spend your time where there is value.
  • DO store comments for tables and columns in the database like this.  The problem with putting data definitions in a project document is they get filed away with the project.  And then someone has to remember what project added a table or column and go look up a document in a separate system.  Use Oracle's built in commenting feature so the definitions you create will be at your fingertips when ever you need them.
  • DON'T ignore existing tables in your database for commenting.  We've all been assigned to take over an application and had occasion when we had to reverse engineer what a table or column really meant.  That is the hard work.  If you go through all of this effort, take the simple step of storing what you discovered as a comment on the table or column.  This way it will be written down somewhere for three months from now when you need to remember the definition again.
  • DO realize that documenting your system is an important part of your job.  Hey, not every part of our job is fun.  That is why it is called work.  But you do have a responsibility to other professionals in your organization to record some basic notes about how you designed and built your database.  You don't have to write Shakespeare, but some basic, functional docmentation is not too much to ask.

The full documentation for the COMMENT statement in Oracle 12c is here.

Thursday, April 9, 2015

Why I Love Being a Pluralsight Author

Pluralsight was in the news today for a couple of reasons.  First, Lynda.com, a competitor was bought by Linked In.  Second, Pluralsight announced they had raised some additional capital.  The combination of the two stories led to inevitable talk about company valuations.  I think this is bound to happen when 1) there is M&A activity going on in the broader market and 2) you are private company who has seen spectacular growth over your lifespan.

Consequently, financial measures were in the headlines today.  And when people find out you are an author, they usually associate being an author with a lot of financial success and large royalty payments.  There is no doubt, being part of a company that has been as successful from a financial standpoint as Pluralsight is great.  And while I am not a top royalty earning author, the income I earn from my Pluralsight courses is a nice stream of extra income.   In our society, we tend to associate financial success with overall personal success and satisfaction.  That is a simplistic view though that often is simply not true.

What I love about being a Pluralsight author, what I really love has nothing to do with money.  It is that in some way, I'm helping hundreds, maybe thousands of people realize their career dreams, to be better at their jobs and to achieve their goals.  Those of us who have been in technology know that it is hard.  It is hard to learn new things and it is hard when you just can't figure out why something doesn't work.  If the courses I do help someone get over that hurdle, get through that rough patch, then I have made a difference for someone, and that is the most valuable reward that I can ever ask for.

Six weeks ago, I attended the Pluralsight Author's Summit in Salt Lake City, and what I found was that I am not alone in feeling that.  Every author I talked to took real interest in wanting to help whoever would click play on their course be a better developer, a better IT Pro, a better creative professional.  You could describe it as a passion, yes, but that word gets a little overused these days.  A better way to describe it might be that embedded in all of us who are associated with Pluralsight, there is a deep sense of purpose to help our colleagues throughout the industry grow and learn and most importantly, realize their dreams.

Financial success is such an easy yardstick to use that sometimes, it becomes the only measuring stick we use to measure the value both of companies and individuals.  Much harder to measure is the little differences someone has made to all of the people who have watched a course and become a better person from what they learned in that course.  Maybe that means someone gets a raise at their job or gets assigned to a project that they really wanted to be on.  Maybe someone who watched a course I or someone else did has more confidence at work tomorrow or has a deeper satisfaction about the project they created.

Those measures matter.  The fact that I get to touch the lives of others all around the world and help them be better at what they do might not ever make headlines, but it means the world to me, and it is what I think about every day.  And I think anyone associated with Pluralsight would tell you the same thing.

That is what I love about having the opportunity to be a Pluralsight author.