How to document a database

Question

(Note: I understand that this is close to How do you document the structure of your database? But I don't think it is identical.)

I started working in a place with a database with literally hundreds of tables and views, all with cryptic names with very few vowels and no documentation. They also do not allow random changes to the database schema, nor can they touch any database other than the test one on my own machine (which is reset and recreated regularly), so I cannot add comments that could help anyone or.

I tried using Toad to create an ER diagram, but leaving it for 48 hours in a row, it still didn't give out anything, and I needed my computer. I talked with some other recent collaborators, and we all suggested that whenever we are confused about what a particular table or some of its columns is, we should update it on the developer wiki.

So what is a good way to do this? Just list the tables / views and their columns and fill them in when we go? The main tools I need are Toad, Oracle "SQL Developer", MS Office and Visio.

Answer 1

In my experience, ER (or UML) diagrams are not the most useful artifact - with a large number of tables, diagrams (especially reverse engineering ones) often represent a big confusing mess from which no one will learn anything.

For my money, some good human-readable documentation (possibly supplemented by diagrams of smaller parts of the system) will give you the greatest mileage. This will include for each table:

• Descriptions of what the table means and how it is functionally used (in the user interface, etc.).
• Descriptions of what each attribute means, if not obvious
• Explanations of relationships (foreign keys) from this table to others and vice versa
• Explanations of additional restrictions and / or triggers
• An additional explanation of the main types and processes that relate to the table, if they are not already documented

With all of the above, do not document for the sake of documentation - documentation that repeats the obvious just hits humanly. Instead, focus on what first confused you, and spend a few minutes writing really clear and concise explanations. This will help you think through this, and it will actively help other developers who come across these tables for the first time.

As already mentioned, there are many tools that can help you deal with this, such as Enterprise Architect , Red Gate SQL Doc and built-in tools from different vendors. But while tool support is useful (and even critical, in large databases), the hard work of understanding and explaining the conceptual model of the database is a real victory. From this point of view, you can even do this in a text file (although doing it in a Wiki form would allow several people to collaborate when adding to this documentation in stages - so, every time someone finds out something, they can add him into the growing body of documentation instantly).

Answer 2

We use Enterprise Architect for our database definitions. We include stored procedures, triggers, and all table definitions defined in UML. Three brilliant features of the program:

• Import UML diagrams from an ODBC connection.
• Generate SQL scripts (DDL) for the entire database at once
• Creating custom template documentation for your database.

You can edit the class / table definitions in the UML tool and generate a fully descriptive document with the image included. An auto-generated document can be in several formats, including MSWord. We have only 100 tables in our schema, and this is quite manageable.

I have never been impressed with any other tool in 10 years as a developer. EA supports Oracle, MySQL, SQL Server (multiple versions), PostGreSQL, Interbase, DB2, and Access in one fell swoop. Every time I had problems, their forums quickly answered my problems. Highly recommended!

When database changes occur, we do this in EA, generate SQL and check it for our version control (svn). We use Hudson to build, and it automatically creates a database from scripts when it sees that you modified the registered sql.

( Mostly stolen from my other answer )

Answer 3

In our team, we came up with a useful approach to documenting old Oracle and SQL Server databases. We use Dataedo to document database schema elements (data dictionary) and create ERD diagrams. Dataedo comes with a documentation repository, so your entire team can work on documenting and reading recent documentation online. And you do not need to interfere with the database (Oracle or SQL Server MS_Description comments).

First, you import the schema (all tables, views, stored procedures and functions — using triggers, foreign keys, etc.). Then you define logical domains / modules and group all objects (drag and drop) into them to be able to analyze and work with smaller fragments of the database. For each module, you create an ERD chart and write a top-level description. Then, when you discover the meaning of tables and views, write a short description for each. Do the same for each column. Dataedo allows you to add a meaningful name for each object and column — useful if the names of the objects are vague or invalid. Pro allows you to describe foreign keys, unique keys / restrictions and triggers - which is useful, but not necessary for understanding the database.

You can access the documentation through the user interface or export it to PDF or interactive HTML (the latter is only available in the Pro version).

It describes a continuous process, not just one time. If your database is changing (for example, new columns, views), you should regularly synchronize your documentation (a couple of clicks using Dataedo).

See sample documentation: http://dataedo.com/download/Dataedo%20repository.pdf

Some recommendations on the documentation process:

Charts:

• Keep your charts small and readable - just include important tables, relationships and columns - only those that have any meaning for understanding the big picture - primary / business keys, important attributes and relationships,
• Use a different color for the key tables in the diagram,
• You can have more than one chart for each module,
• You can add a chart to the description of the most important tables / with most relationships.

Descriptions:

• Do not document the obvious - do not write the “Document Date” description for the document.date column. If there is nothing significant to add, just leave it blank,
• If the objects stored in the tables have types or statuses, it is useful to list them in the general description of the table,
• Define the format that is expected, for example. "mm / dd / yy" for the date that is stored in the text box,
• A list of all known / important values ​​and their meaning, for example. for the status column, there might be something like this: "Document Status: A - Active, C - Canceled, D - Deleted",
• If any API for the table is a view that should be used to read data and functions / procedures for inserting / updating data, list it in the table description,
• Describe where the rows / columns come from (procedure, form, interface, etc.),
• Use the "[deprecated]" sign (or similar) for columns that should not be used (the column field is useful for this, explain which field should be used instead in the description field).

Answer 4

One thing to consider is the COMMENT tool built into the DBMS. If you post comments on all tables and all columns of the DBMS itself, your documentation will be in the database system.

Using the COMMENT tool does not make any changes to the schema, it only adds data to the catalog table USER_TAB_COMMENTS.

Answer 5

This answer extends Kiveli above, which I supported. If your EA version supports Object Role Modeling (conceptual design versus logical design = ERD), reverse engineer this and then fill the model with the expressive richness that it gives you.

A cheaper and easier option is to download Visiomodeler for free from MS and do the same with it.

ORM (call it ORMDB) is the only tool I have ever found that supports and encourages conversations about database design with non-IS stakeholders, about BL objects and relationships.

Verification of reality - on the way to generating DDL, it goes through the phase of a complete stop of ERD, where you can satisfy your questions about the fact that she is doing something sharp. This is not true. This will probably show you the weak points in the ERD that you yourself created.

ORMDB is a classic case of the principle: the more conceptual the tool, the smaller the market. Girls just want to have fun, and programmers just want to code.

Answer 6

Here is a good article on how to approach database documentation: http://www.simple-talk.com/sql/database-administration/database-documentation---lands-of-trolls-why-and-how/

Answer 7

The wiki solution supports hyperlinks and collaborative editing, but the wiki is no worse than the people who support and update it. You need someone to own the draft document, no matter what tool you use. This person may engage other knowledgeable people to fill out the details, but one person should be responsible for organizing the information.

If you cannot use the tool to create an ERD using reverse engineering, you will have to create it manually using TOAD or VISIO.

Any ERD with hundreds of objects is probably useless as a guide for developers, because it will be unreadable with so many boxes and rows. In a database with so many objects, there are probably "subsystems" of several dozen tables and views of each. Therefore, you should create your own diagrams of these subsystems, instead of expecting the tool to do it for you.

You can also create pseudo-ERDs where groups of tables are represented by one object in one diagram, and this group is expanded in another diagram.

A single ERD or a set of ERDs is not enough to document a system of this complexity, no more than a class diagram, sufficient to document an OO system. You will need to write a document using ERD as illustrations. You need textual descriptions of the meaning and use of each table, each column, and the relationships between the tables (especially where such relationships are implicit and not represented by referential integrity constraints).

All this is a lot of work, but it's worth it. If there is a clear and modern place where the scheme is documented, the whole team will benefit from it.

Answer 8

Since you have the luxury of working with other developers who are in the same boat, I would suggest asking them how they feel, transmitting the necessary information, most easily. My company has over 100 tables, and my boss gave me ERD for certain set tables that all connect. So you can try breaking 1 massive ERD into a bunch of smaller managed ERDs.

Answer 9

If describing your databases to end users is your primary goal, the Ooluk Data Dictionary Manager may be useful. This is a web-based multi-user software that allows you to attach descriptions to tables and columns and allows you to perform full-text search on these descriptions. It also allows you to logically group tables using labels and view tables using these labels. Tables as well as columns can be marked to search for similar data items in your database / databases.

The software allows you to import metadata data, such as table name, column name, column data type, foreign keys into the internal repository using the API. JDBC data source support is built-in and can be expanded as the API source is distributed in ASL 2.0. It is encoded to read COMMENTS / COMMENTS from many RDBMS. You can always manually override imported information. The information you can store about tables and columns can be expanded using custom fields.

The data dictionary manager uses the terminology “data object” and “attribute” instead of a table and column because it is not specifically designed for relational databases.

Notes

• When describing the technical aspects of your database, such as triggers, indexes, statistics, it is important that this software is not the best option. However, you can combine the technical solution with this software that uses custom hyperlink fields.
• Software does not create ERD

Disclosure: I work for a company that develops this product.

Answer 10

Well, the image says a thousand words, so I would recommend creating ER charts where you can immediately see the relationship between the tables, which is difficult to do with text-only description.

You do not need to make the entire database on one diagram, divide it into sections. We use Visual Paradigm at work, but EA is a good alternative, like ERWIN, and there are undoubtedly many others that are just as good.

If you have patience, then using html to document tables and columns makes access to documentation easier.