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?
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:
- Comments
- Comments in RDBMS
- 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.
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 🙂
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.
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.
16 Comments
In which table in Oracle is stored the information from the field “Comments in RDBMS”?
Lots of views for perusing the data dictionary, never look at the tables directly.
I’d start with all_tables and all_tab_cols
Or in other words, when I save a comment in the SQL Developer Modeler on a comment in the field “Comments in RDBMS” I dont see it in the table all_col_comments and vice versa.
Because your updating your data model and not the database – two VERY different things.
One is living and breathing, and another is a set of plans, ‘on paper.’
Like blueprints vs the actual house you built using those blueprints.
In all_tables and all_tab_cols? But there I dont see any comment columns.
ok, fine, but is there a way to show on the user defined model reports the comment from the DB from table all_col_comments.column_name?
Connect to the database..run the appropriate reports from there.
Or, synchronize your data model with the database – that will update your model to show what’s in the database already.
Yes, a synchronization was needed, that helped, thanks. Comments are saved in all_tab_comments/all_col_comments. Another question: there is menu called “Export diagram to Database”. In which table is stored this information?
Multiple tables, as discussed here.
If I am putting a RDBMS comment on a primary key column of a table, how do I ensure it flows into corresponding foreign key in its child table?
The comment? Not sure I understand…
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
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
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.
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.
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!