SQL Developer Data Modeler: On Notes, Comments, and Comments in RDBMS

thatjeffsmith SQL Developer 5 Comments

Tell Others About This Story:

Ah the beautiful data model. They say a picture is worth a 1,000 words. And then we have our diagrams, how many words are they worth?

Our friends from the Human Relations sample schema

Our friends from the Human Relations sample schema

So our models describe how the data ‘works’ – whether that be at a logical-business level, or a technical-physical level. Developers like to say that their code is self-documenting. These would be very lazy or very bad (or both) developers.

Models are the same way, you should document your models with comments and notes!

I have 3 basic options:

  1. Comments
  2. Comments in RDBMS
  3. Notes

So what’s the difference?

Comments

You’re describing the entity/table or attribute/column. This information will NOT be published in the database. It will only be available to the model, and hence, folks with access to the model.

Table Comments (in the design only!)

Table Comments (in the design only!)

Comments in RDBMS

You’re doing the same thing as above, but your words will be stored IN the data dictionary of the database. Oracle allows you to store comments on the table and column definitions. So your awesome documentation is going to be viewable to anyone with access to the database.

RDBMS is an acronym for Relational Database Management System – of which Oracle is one of the first commercial examples 🙂

If the DDL is produced and ran against a database, these comments WILL be stored in the data dictionary.

If the DDL is produced and ran against a database, these comments WILL be stored in the data dictionary.

Notes

A place for you to add notes, maybe from a design meeting. Or maybe you’re using this as a to-do or requirements list. Basically it’s for anything that doesn’t literally describe the object at hand – that’s what the comments are for.

I totally made these up.

I totally made these up.

Now these are free text fields and you can put whatever you want here. Just make sure you put stuff here that’s worth reading. And it will live on…forever.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 5

  1. Very close (and very cool feature), but I can’t get the notes to display, the displayed columns (rows) are: Table Name, Functional Name, Abbreviation, Classification Type Name and Object Type Name

    1. thatjeffsmith Post
      Author

      Sorry Chet for taking so long to get back to you.

      So, to find tables with notes, and then export them out…

      Advanced Search, note that the Notes field is set to ‘Not NULL’ basically

      And then do the search, we get the results

      And then hit the ‘Report’ button. First, I create a custom template that just contains the table name, and its notes

  2. Is there an easy way to extract the notes? Or identify which tables/columns have notes on them?

    I’m debating whether to use that section for my actual notes or just use Excel. I’d prefer them be in the tool with everything else, but having to drill down is painful, especially if I want to share those notes.

    1. thatjeffsmith Post
      Author

      For sure, you can do a search on entities-attributes/tables-columns with comments or notes, and then export those to Excel. So do a ‘not null’ type search as I talk about here…if you need help I can do up a post for this specific use case.

  3. Nice post as always!

    It is worth noting that Comments in RDBMS for Tables, Columns, and Views automatically get generated in the DDL (if there are any) unless you turn that option off (but why would you?).

    For those that don’t know, not only will SDDM pick up comments in the db during reverse engineering, but most modern BI tools (like OBIEE, Cognos, Business Objects, and even Oracle Discoverer) will also pick these up and automatically include them in the BI tool’s metadata layer for end users to access. So be careful what you write!

Leave a Reply

Your email address will not be published. Required fields are marked *