Oh Yeah, Data – Poor it on!
What’s a company without lots of little bits of information that need to be managed? Like the Class Diagram and Sequence Diagram, the Data Dictionary is invaluable when the system is handed-off to new developers or a third party integration team. Instead of spending hours using SQL Query Analyzer or Enterprise Manager trying to gain an overview of the relationships, your document should offer a quick read about how the system manages its data. Here is a document walk through that you may find helpful.
Before you begin, check with the DBA to insure you have a user name and password to access the current server and the database. (If he gives you “sa” without a password, don’t tell anyone; just cry.) Pre-supposing you’re working with a Microsoft SQL system, open the SQL Server Enterprise Manager (version 8.0). Depending on how the manager is configured you may have to click a ways to get to your tables.
When you arrive at the server name, click the icon bearing the server name and the manager will open the files belonging to that server. Click “Databases”. From the list choose the database you are about to document. Choose “Tables”. Click the “Type” column until all the “User” type tables are at the top of your list.
The first thing you will usually notice is a lot of names that have the same prefixes. You should ask the DBA or developer to familiarize you with the naming convention of the “User” type tables. Document that convention at an appendix to the Data Dictionary. When I create table names, I like placing an underscore before the table name. That way I can identify my tables at a glance. However, every company has their own naming convention and this should be noted.
Right click the name of the user defined table name and choose “Properties”. In the “General” tab note the column name, their data types, size (length) and if they allow NULLS (required). Note the primary key (PK) now. We’ll get to the foreign key constraints (FK) later. Click the “Full-Text Indexing” tab to document the indexed columns. Next, document the permissions. Click the “General” tab again and click the “Permissions” button. Note the permissions as they appear in the “Permissions” tab.
To document the foreign key constraints (FK), close the “Tables Properties” form and click the “Diagrams” icon in the “console” window to the left. Normally, the developer or DBA will have already created a relationship diagram. If there is more than one, ask the master blaster person who created the diagrams which one contains a complete overview of the relationships.
Once you’ve found the correct diagram, right click your way through the table names. Choose each time “Properties” from the drop-down menu. Choose the “Relationships” tab. First check how many relationships your table may have by clicking on the “Selected relationship” drop-down list. Choose the top item in the list. Copy the name of that relationship as it appears in the “Relationship name” text box. Find the table’s name in your dictionary. Now paste the relationship name in the section entitled “Foreign Key Restraints” in your dictionary. Do this for each relationship in the drop-down list. If no relationships appear in the “Relationships” tab, then there are none.
If there are no diagrams, you may create one by right clicking the empty diagram window to the right choosing “New Database Diagram” from the drop-down menu. Work the “Create Database Diagram Wizard” through. At the section entitled “Select Tables to be Added”, simply mark all the “Available tables” to the left of the pane and add them all to the “Tables to add to diagram” pane to the right. Click “next”. The wizard may warn you that it will take a while to create the diagram, but since you don’t have a clue as to which tables contain relationships and which don’t. Your options are limited. Go for a soda or some water. It usually doesn’t take very long, unless you have some very large tables.
Now to finish the database documentation set, enter some sample data. To save a lot of time, run a simple SELECT query on the table. Here’s how you can do this between now and when you become a SQL query pro.
Click the “Tables” icon in the left console window and click on the “Table” icon under the database you are documenting. In the right pane of the Enterprise Manager, right click the table you want to document. From menu choose “Open Table | Choose Top”¦”. The “Number of Rows” text box appears. Enter a number like 3 in the text area and click the “OK” button. The system runs the SELECT query for you on the three top records.
In the Data Dictionary that reports the architecture of the table you choose, go to the final section entitled “Data Example”. I’ve used, a place holder text entitled “Column Name Here”. Overwrite this text with the names of the columns returned by your query. The first column name could look something like PubLanguage. Under the field name, enter the 3 results returned from your SQL Query. Here I’ve entered some place holder text entitled “Sample Data Here”. In this example the query results may read “Slovensko”, “Deutsch” and “English”.
Heed this word of warning! Do not, under any circumstances, use real personal data or names of people. Mix-up names or use “First Name” “Last Name”. If a field name contains Social Security, telephone for fax numbers, use an “x” to represent the numbers and don’t add any character that is not in the original record. If the telephone number is 3333333333 then you may enter xxxxxxxxxx. Do not enter xxx-xxx-xxxx. This may mislead the DBA or developer as to the data type used. That should just about do it.
As with the Class and Sequence diagrams, you don’t have to be a developer or DBA to document the data architecture, but if you want to become a more valuable asset then you should learn as much as you possibly can in both the field of writing programs and developing databases. Sure it’s a lot of work, but so is living. Just blame the inconvenience on Adam and Eve or someone.
Articles Additional Resources
Here are some great places to begin your journey to understanding databases.
Wikipedia Article on Normalization
Database Normalization by Mike Chapple
Rules of Data Normalization by Data Model.org
Template
This document may not contain everything required in a specific dictionary, but it does cover all the major areas.
Flash Paper
PDF
Word Template